我们可以通过一个查询从五个具有共同ID的表中获取数据吗？

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

我想从一个查询中获取具有共同id的五个表的数据,我们可以这样做,

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

例如tbl_student,tbl_batch,tbl_section,tbl_level,tbl_faculty都有一个共同的id college_id

how can i get all the tables value with one query

如何通过一个查询获取所有表值

if anybody can help me i would be greatful

如果有人能帮助我,我会很高兴

3 个解决方案

#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.

这很可能不是您想要获取数据的方式,因为数据模型没有多大意义。希望你能得到这个想法。

#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

#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.

您的SQL查询返回一个二维表,每行包含相同的列。如果你的某些行是学生而其中一些是院系,那么会有一堆对学生有意义但对某些院系没有意义的列,对于教师行,这些列应该为空。

The SQL to do this for two tables is:

为两个表执行此操作的SQL是:

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.

这对我来说没有多大意义。

#1