how to select data from multiple tables?

Question

I have 4 tables, i am very confused to select the data.

I have this fields in my 4 tables

t1 -> student_id
  given_name
  user_name

t2 -> school_year
  sem
  class
  student_id
  main_id

t3 -> subject_id
  main_id

t4 -> subject_id
  subject_name

I need to select (given_name, subject_name, class & main_id). How?

I try like this

SELECT t2.school_year, t2.rp_main_sem, t2.rp_main_class,  t4.name, t4.subject_id, t1.given_name, t1.user_name FROM t1, t2, t3, t4 WHERE t2.school_year = 2011 AND t2.sem = 1 AND t2.class = 'ES3A' AND t3.subject_id = t4.subject_id AND t1.student_id = t2.rp_main_student_id

`SELECT t1.given_name, t4.subject_name, t2.class, t2.main_id FROM t1, t2, t4` — pb149, Sep 15 '11 at 08:16
@apssiva: I added the `WHERE` clause to my query, but if you need to add something to your question, you must click on the `edit` button below your question to add the extra details. — Shef, Sep 15 '11 at 08:34

score 0 · Answer 1 · answered Sep 15 '11 at 08:19

0

I guess you need a query like this:

SELECT t1.given_name, t4.subject_name, t2.main_id
FROM t1
LEFT JOIN t2 ON t1.student_id = t2.student_id
LEFT JOIN t3 ON t2.main_id = t3.main_id
LEFT JOIN t4 ON t3.subject_id = t4.subject_id

answered Sep 15 '11 at 08:19

Teneff

30,564
13
72
103

Konerak · Answer 2 · 2011-09-15T08:24:57.690

You will need to JOIN the tables. Each table should have a PRIMARY (or UNIQUE) key, and references (so called FOREIGN KEYs) to the other tables.

SELECT given_name , t4.subject_name, t2.class, t3.main_id
FROM t1 
JOIN t2 
ON t1.student_id = t2.student_id
JOIN t3
ON t2.main_id = t3.main_id
JOIN t4
ON t2.subject_id = t3.subject_id
WHERE t2.school_year = "2011""  and t2.sem = 1 --added where because of your comment

So we assume that

the students' table t1 has PK student_id
the subjects' table t4 has PK subject_id
t2 is a associative table that links students (using their student_id) to classes, which have a main_id.
t3 is an associative table that links subjects to classes.

score 0 · Answer 3 · answered Sep 15 '11 at 08:19

   SELECT
      t1.given_name,
      t3.subject_name,
      t2.class,
      t2.main_id 
   FROM
      t1 
   JOIN
      t2 
         ON t1.student_id = t2.student_id 
   JOIN
      t3 
         ON t2.main_id = t3.main_id 
   JOIN
      t4 
         ON t3.subject_id = t4.subject_ID

this should do it but please tell us in what form you need the data. do you want to get the classes per student? or students per class?

score 0 · Answer 4 · edited Sep 15 '11 at 08:48

0

select t1.given_name, t4.subject_name, t2.class, t2.main_id
  from t1 
  join t2 on t1.student_id = t2.student_id
  join t2.main_id = t3.main_id
  join t3.subject_id = t4.subject_id

edited Sep 15 '11 at 08:48

axel22

32,045
9
125
137

answered Sep 15 '11 at 08:19

Saket

45,521
12
59
79

Shef · Answer 5 · 2011-09-15T08:33:26.227

0

Try:

   SELECT t1.given_name, t4.subject_name, t2.class, t2.main_id
     FROM t1
LEFT JOIN t2
       ON t1.student_id = t2.student_id
LEFT JOIN t3
       ON t2.main_id = t3.main_id
LEFT JOIN t4
       ON t3.subject_id = t4.subject_id
    WHERE t2.school_year = 2011 
      AND t2.sem = 1

edited Sep 15 '11 at 08:33

answered Sep 15 '11 at 08:20

Shef

44,808
15
79
90

score 0 · Answer 6 · answered Sep 15 '11 at 08:28

SELECT table_name1.given_name,table_name4.subject_name,
table_name2.class,table_name2.main_id
FROM table_name1
INNER JOIN table_name2
ON table_name1.studen_id=table_name2.studen_id
INNER JOIN table_name3
ON table_name2.main_id=table_name3.main_id
INNER JOIN table_name4
ON table_name3.subject_id=table_name4.subject_id

how to select data from multiple tables?

6 Answers6