SQL 如何根据多表选择SQL结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6587514/
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
How to select SQL results based on multiple tables
提问by cfrederich
I need to select results from one table based on certain matching values in a couple of other tables. I have the following tables:
我需要根据其他几个表中的某些匹配值从一个表中选择结果。我有以下表格：
person: id, firstname, lastname
team: id, teamname
player: id, person_id(FK), team_id(FK)
coach: id, person_id(FK), team_id(FK)
I need to return all the coaches and players names for each team. I've only ever used inner joins, and it doesn't seem like I can use those here, so any idea how to do this?
我需要返回每支球队的所有教练和球员姓名。我只使用过内部连接，似乎我不能在这里使用它们，所以知道如何做到这一点吗？
回答by Chris Cunningham
This will give you the coach:
这会给你教练：
SELECT team.Teamname, person.Firstname, person.Lastname
FROM person
JOIN coach ON person.id = coach.person_id
JOIN team ON coach.team_id = team.id
And this will give you the players:
这会给你的球员：
SELECT team.Teamname, person.Firstname, person.Lastname
FROM person
JOIN player ON person.id = player.person_id
JOIN team ON player.team_id = team.id
So, the non-elegant, simple answer is to just toss it all together with UNION.
因此，不优雅、简单的答案是将它们全部与UNION.
回答by Conrad Frix
Just use an OR in the join to Team
只需在加入团队时使用 OR
SELECT
P.firstname,
P.lastname,
T.teamname
FROM
person p id
LEFT JOIN player pl
ON p.id = pl.person_id
LEFT JOIN coach c
ON p.id = c.person_id
LEFT JOIN team t
ON pl.team_id = t.id
or.c.team_id = t.id
Or if you perfer if and your database has COALESCE
或者，如果您更喜欢您的数据库是否有 COALESCE
LEFT JOIN team t
ON COALESCE(pl.team_id,c.team_id) = t.id
