CODEWITHSUNDEEP
phpmysqlsqlleft-join
user2605793
user2605793

Reputation: 471

Mysql multiple left joins on same table

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?

Upvotes: 9

Views: 28659

Answers (1)

Lajos Veres
Lajos Veres

Reputation: 13725

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 ...

Upvotes: 20

Related Questions