MySQL LEFT JOIN 3 个表

2021-11-20 00:00:00 join sql mysql

我有 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

相关文章