问题描述
我有 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
我在这里做错了什么?
推荐答案
您正在尝试将 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
之间的join 是一个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 个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!