Multiple Left Mysql Joins on One Table

Question

I have a table with two fields that reference the ID of another table. I need to derive a name from another 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 say 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 I put in these last two statements to get two names. Or, more precisely, what do I need to add to SELECT to indicate that I want two different versions of the NAME field from table 2 to be called?

Answer 1

You must also field aliases:

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

Answer 2

 SELECT xxx, p1.NAME AS p1Name, p2.NAME AS p2Name FROM Table1 $worker1name = $rec['p1Name']; $worker2name = $rec['p2Name'];