MySQL LEFT JOIN 3 个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16222097/
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
MySQL LEFT JOIN 3 tables
提问by Joe
I have 3 tables:
我有3张桌子：
Persons (PersonID, Name, SS) Fears (FearID, Fear) Person_Fear (ID, PersonID, FearID)
Now I'd like to list every person with whatever fear is linked to them (can be multiple fears but can also be none). The persons table has to be shown even if a person doesn't have a fear linked to them.
现在我想列出每个人都有与之相关的恐惧（可以是多种恐惧，也可以是无恐惧）。即使一个人没有与他们相关的恐惧，也必须显示人员表。
I think I need to do a LEFT JOIN, but my code doesn't seem to work:
我想我需要做一个 LEFT JOIN，但我的代码似乎不起作用：
SELECT persons.name,
persons.ss,
fears.fear
FROM persons
LEFT JOIN fears
ON person_fear.personid = person_fear.fearid
What am I doing wrong here?
我在这里做错了什么？
回答by Ant P
You are trying to join Person_Fear.PersonIDonto Person_Fear.FearID- This doesn't really make sense. You probably want something like:
您试图加入Person_Fear.PersonID到Person_Fear.FearID-这并不是真正意义。你可能想要这样的东西：
SELECT Persons.Name, Persons.SS, Fears.Fear FROM Persons
LEFT JOIN Person_Fear
INNER JOIN Fears
ON Person_Fear.FearID = Fears.FearID
ON Person_Fear.PersonID = Persons.PersonID
This joins Personsonto Fearsvia the intermediate table Person_Fear. Because the join between Personsand Person_Fearis a LEFT JOIN, you will get all Personsrecords.
此连接Persons到Fears经由中间表Person_Fear。因为Personsand之间的连接Person_Fear是 a LEFT JOIN，您将获得所有Persons记录。
Alternatively:
或者：
SELECT Persons.Name, Persons.SS, Fears.Fear FROM Persons
LEFT JOIN Person_Fear ON Person_Fear.PersonID = Persons.PersonID
LEFT JOIN Fears ON Person_Fear.FearID = Fears.FearID
回答by echo_Me
try this
尝试这个
SELECT p.Name, p.SS, f.Fear
FROM Persons p
LEFT JOIN Person_Fear fp
ON p.PersonID = fp.PersonID
LEFT JOIN Fear f
ON f.FearID = fp.FearID
回答by Tanmay Patel
Try this definitely work.
试试这个绝对有效。
SELECT p.PersonID AS person_id,
p.Name, p.SS,
f.FearID AS fear_id,
f.Fear
FROM person_fear AS pf
LEFT JOIN persons AS p ON pf.PersonID = p.PersonID
LEFT JOIN fears AS f ON pf.PersonID = f.FearID
WHERE f.FearID = pf.FearID AND p.PersonID = pf.PersonID
回答by Aheho
Select
p.Name,
p.SS,
f.fear
From
Persons p
left join
Person_Fear pf
inner join
Fears f
on
pf.fearID = f.fearID
on
p.personID = pf.PersonID
回答by user9905475
Select Persons.Name, Persons.SS, Fears.Fear
From Persons
LEFT JOIN Persons_Fear
ON Persons.PersonID = Person_Fear.PersonID
LEFT JOIN Fears
ON Person_Fear.FearID = Fears.FearID;
