SQL 左连接多个表到一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21126181/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Left join multiple tables onto one table
提问by tumchaaditya
I want to left join multiple tables to one table. The tables are themselves results of subqueries.
我想将多个表左连接到一个表。这些表本身就是子查询的结果。
A classical example that comes to my mind is, I have a bunch of subqueries:
1. Subquery A gives me details of students - say table 1
2. Subquery B gives me student scores in Math - say table 2
3. Subquery C gives me student scores in English - say table 3
The tables contain scores only if the student has taken that test and the student is to be considered failed if he/she has not taken test at all (or has a score < passing score). I have student IDs (unique per person) in each table to join on.
我想到的一个经典例子是，我有一堆子查询：
1. 子查询 A 给了我学生的详细信息——比如表 1
2. 子查询 B 给了我学生的数学分数——比如表 2
3. 子查询 C 给了我学生英语成绩——比如表 3
表格仅包含学生参加了该考试的成绩，如果学生根本没有参加考试（或分数<及格分数），则该学生将被视为不及格。我在每张桌子上都有学生证（每人唯一）加入。
What do I want from these? I am trying to build a dynamic query (where some parts are populated at runtime by an external mechanism) by performing some joins on these tables to give me:
1. Students who passed in both tests and corresponding scores
2. Students passed in either test, but failed (or did not take) the other test and the corresponding scores (NULL if not taken).
3. All students and their corresponding scores.
我想从这些中得到什么？我正在尝试通过在这些表上执行一些连接来构建动态查询（其中某些部分在运行时由外部机制填充），以给我：
1. 通过两个测试和相应分数的
学生 2. 学生通过任一测试，但未通过（或未参加）其他测试和相应的分数（如果未参加则为 NULL）。3. 所有学生及其相应分数。
What I have on mind is left joining eachscore table to student profile table. How should I go about this?
我想到的是将每个分数表加入到学生资料表中。我应该怎么做？
Before you go ahead and suggest table 1 left join table 2 left join table 3, this structure will cause problems if, say table 2 contains a null record for a particular student (as per my knowledge). And this basically joins table 3 on table 2 and not on table 1, from my understanding, which is what I want.
在您继续提出建议之前table 1 left join table 2 left join table 3，如果例如表 2 包含特定学生的空记录（据我所知），则此结构将导致问题。根据我的理解，这基本上将表 3 连接到表 2 而不是表 1，这就是我想要的。
PS: Feel free to suggest better ways to get what I need, if you know any.
PS：如果你知道的话，请随意提出更好的方法来获得我需要的东西。
回答by Barranka
You can create the appropriate relations by writing carefully your query:
您可以通过仔细编写查询来创建适当的关系：
select
t1.*, t2.foo, t3.bar
from
table1 as t1
left join table2 as t2 on t1.id = t2.id
left join table3 as t3 on t1.id = t3.id
As you can see, table3is related to table1(not to table2), which is what you want.
如您所见，table3与table1（而不是table2）有关，这就是您想要的。
