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



Question:

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


Solution: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.


Solution: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  


Solution: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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »