MySQL LEFT JOIN 3 tables

Question

I have 3 tables:

  Persons (PersonID, Name, SS)
 Fears (FearID, Fear)
 Person_Fear (ID, PersonID, FearID) 

Now I would like to list each person with some kind of fear associated with them (there can be many fears, but there can also be a nickname). A table of persons should be shown, even if a person does not have the fear associated with them.

I think I need to do a LEFT JOIN, but my code does not 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? Thanks in advance.

Answer 1

You are trying to join Person_Fear.PersonID on Person_Fear.FearID - this makes no 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 connects to Persons on Fears through the staging table Person_Fear . Since the connection between Persons and Person_Fear is LEFT JOIN , you will get all Persons records.

As an alternative:

 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 

Answer 2

try it

  SELECT p.Name, p.SS, f.Fear FROM Persons p LEFT JOIN Person_Fear fp ON p.PersonID = fp.PersonID LEFT JOIN Fear f ON f.FearID = fp.FearID 

Answer 3

Try it definitely.

 SELECT p.PersonID AS person_id, p.Name, p.SS, f.FearID AS fear_id, f.Fear FROM person_fear AS pf LEFT JOIN persons AS p ON pf.PersonID = p.PersonID LEFT JOIN fears AS f ON pf.PersonID = f.FearID WHERE f.FearID = pf.FearID AND p.PersonID = pf.PersonID 

Answer 4

 Select p.Name, p.SS, f.fear From Persons p left join Person_Fear pf inner join Fears f on pf.fearID = f.fearID on p.personID = pf.PersonID