Mysql multiple left joins on same table
24,625
You should alias the fields also like this:
SELECT
xxx,
p1.NAME as p1name,
p2.NAME as p2name
FROM Table1
LEFT JOIN Table2 AS p1 ON Table1.worker1 = p1.ID
LEFT JOIN Table2 AS p2 ON Table1.worker2 = p2.ID
WHERE ...
Author by
user2605793
Updated on July 09, 2022Comments
-
user2605793 almost 2 years
I have a table with two fields that reference the ID of another table. I need to pull the name from the other table for both fields.
eg.
Table1 worker1 = 2 (2 is key to other table) worker2 = 4
Table2 ID NAME 1 Bill 2 Fred 3 John 4 Paul
I need to get $worker1name = Fred and $worker2name = Paul.
So I will be saying something like:
SELECT xxx, NAME?, NAME? FROM Table1 LEFT JOIN Table2 AS p1 ON Table1.worker1 = Table2.ID LEFT JOIN Table2 AS p2 ON Table1.worker2 = Table2.ID WHERE ... $table = mysql_query(...); $rec = mysql_fetch_assoc($table); $worker1name = $rec['???']; $worker2name = $rec['???'];
What do I insert in those last two statements to get the two names. Or more precisely what do I have to add to the SELECT to specify what I want the two different versions of the NAME field from table 2 to be called please?
-
user2605793 over 10 yearsI get an error: Could not run query: Unknown column 'Table2.ID' in 'on clause' (the equivalent names in my table)
-
Lajos Veres over 10 yearsmodified the query. (you have to use the alias is there is any.)