MySQL LEFT JOIN 3表 [英] MySQL LEFT JOIN 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.
我认为我需要做一个LEFT JOIN,但是我的代码似乎不起作用:
I think I need to do a LEFT JOIN, but my code doesn't seem to work:
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?
推荐答案
您正试图将Person_Fear.PersonID
加入到Person_Fear.FearID
上-这实际上没有任何意义.您可能想要类似的东西:
You are trying to join Person_Fear.PersonID
onto Person_Fear.FearID
- This doesn't really make sense. You probably want something like:
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
这通过中间表Person_Fear
将Persons
连接到Fears
.因为Persons
和Person_Fear
之间的连接是LEFT JOIN
,所以您将获得所有Persons
记录.
This joins Persons
onto Fears
via the intermediate table Person_Fear
. Because the join between Persons
and Person_Fear
is a LEFT JOIN
, you will get all Persons
records.
或者:
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
这篇关于MySQL LEFT JOIN 3表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!