mysql left join 三表_MySQL LEFT JOIN 3表

I have 3 tables:

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:

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?

解决方案

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

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.

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