can we get the data from five tables having a common id with one query?

Question

hi...

i want to get the data of five table having a common id from one query can we do this ,

for example tbl_student,tbl_batch,tbl_section,tbl_level,tbl_faculty all have a common id college_id

how can i get all the tables value with one query

if anybody can help me i would be greatful

Answer 1

If I understand you correctly that sounds like a join.

select * from tbl_student st 
join tbl_batch ba on ba.college_id=st.college_id
join tbl_section se on se.college_id=st.college_id
join tbl_level le on le.college_id=st.college_id
join tbl_faculty fa on fa.college_id=st.college_id

This is most probably not exactly the way you want to get the data because the data model would not make much sense. Hopefully you get the idea though.

Answer 2

SELECT fields FROM table1
  LEFT JOIN table2 ON table1.id = table2.id
  LEFT JOIN table3 ON table1.id = table3.id
  LEFT JOIN table4 ON table1.id = table4.id

Answer 3

You can do it, but it won't make a lot of sense.

Your SQL query returns a 2-D table with the same columns for each row. If some of your rows are students and some of them are faculties, then there will be a bunch of columns that make sense for students but don't make sense for faculties, and for a faculty row those columns should be null.

The SQL to do this for two tables is:

SELECT t_F.college_id AS college_id, t_F.f_1, t_F.f_2, NULL   , NULL
    FROM tbl_Faculty AS t_F
UNION
SELECT t_S.college_id AS college_id, NULL   , NULL   , t_S.s_1, t_S.s_2
    FROM tbl_Student AS t_S
ORDER BY college_id

Then your results will look like:

college| Faculty field 1 |  2  | Student field 1 | 2
--------------------------------------------------------------
1      | abc             | def | NULL            | NULL
1      | abc             | ghi | NULL            | NULL
1      | NULL            | NULL| asdoifjas       | aosdifjasdf
1      | NULL            | NULL| asdoifjas       | aosdifjasdf
2      | abc321          | aaa | NULL            | NULL
2      | abc456          | bbb | NULL            | NULL
2      | NULL            | NULL| afasdfafs       | aosdifjasdf
2      | NULL            | NULL| asdoifjas       | aoffavsdfff

This doesn't make that much sense to me.