MySQL LEFT JOIN 3 tables

join, mysql, sql

Solution

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

Problem

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?

Original source