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 = 4Table2 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?
Syntax For Left Join:SELECT column names FROM table1 LEFT JOIN table2 ON table1. matching_column = table2. matching_column; Note: For example, if you have a left table with 10 rows, you are guaranteed to have at least 10 rows after applying join operation on two tables.
SELF JOIN syntaxTo perform a SELF JOIN in SQL, the LEFT or INNER JOIN is usually used. SELECT column_names FROM Table1 t1 [INNER | LEFT] JOIN Table1 t2 ON join_predicate; Note: t1 and t2 are different table aliases for the same table. You can also create the SELF JOIN with the help of the WHERE clause.
Yes, indeed! You can use multiple LEFT JOINs in one query if needed for your analysis.
SQL left Join Multiple Tables - Overviewa LEFT JOIN in SQL returns all rows from the left table and only the matched rows from the right. In SQL Left join, if a specific row is present in the left table but not in the right, the result will include this row but with a NULL value in each column from the right.
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 ...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With