CODEWITHSUNDEEP
sqlmysqljoin
jarus
jarus

Reputation: 1873

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

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

Upvotes: 2

Views: 350

Answers (3)

Alex
Alex

Reputation: 1247

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.

Upvotes: 0

Evert
Evert

Reputation: 99687

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

Upvotes: 0

Jonas Elfström
Jonas Elfström

Reputation: 31438

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.

Upvotes: 6

Related Questions