MySQL LEFT JOIN 3 tables

Question

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

User · Answer

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

User · Answer

Select Persons Name  Persons SS  Fears Fear From Persons LEFT JOIN Persons Fear ON Persons PersonID   Person Fear PersonID LEFT JOIN Fears ON Person Fear FearID   Fears FearID

User · Answer

Try this definitely work    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

User · Answer

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

User · Answer

try this      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

User · Answer

SELECT p    f Fear FROM Persons p LEFT JOIN Person Fear pf ON pf PersonID   p PersonID LEFT JOIN Fears f ON f FearID   pf FearID ORDER BY p PersonID    You need to select from the Persons table to ensure you generate a row for every person  whether they have fears or not  Then you can left join Person Fear to every person  which will just be NULL if they don t have any entries  as you want   Finally  you left join Fears on Person Fear so that you can select the name of the fear  Optionally  add an order so that each person has all their fears listed together  even if they were added to the Person Fear table at different times

[mysql] MySQL LEFT JOIN 3 tables

Examples related to mysql

Examples related to sql

Examples related to join