Обсуждение: left join syntax

I have a database that tracks work orders.  Each order tracks two entries
from the employees table;  the employee ID of the person assigned to the
work order and the ID of the person who completed the order.  Each work
order may have one, both, or neither field filled in.  As such, I need to
left join the employee table to the work order table, but I cannot figure
out the syntax for the double-join.  As independent selects, I can do the
join:

SELECT o.ordr_id, a.last_name
FROM ordr o left join employee a on o.assigned_id = a.emp_id;

SELECT o.ordr_id, c.last_name
FROM ordr o left join employee c on o.completion_id = c.emp_id;

But I would like to have the whole thing in a single SELECT.  Can you help
me out?

------------------------------------------------
Haywood J'listzen

"Haywood J'Bleauxmie" wrote: >I have a database that tracks work orders.  Each order tracks two entries >from the
employeestable;  the employee ID of the person assigned to the >work order and the ID of the person who completed the
order. Each work >order may have one, both, or neither field filled in.  As such, I need to >left join the employee
tableto the work order table, but I cannot figure >out the syntax for the double-join.  As independent selects, I can
dothe >join: > >SELECT o.ordr_id, a.last_name >FROM ordr o left join employee a on o.assigned_id = a.emp_id; > >SELECT
o.ordr_id,c.last_name >FROM ordr o left join employee c on o.completion_id = c.emp_id; > >But I would like to have the
wholething in a single SELECT.  Can you help >me out?
 

Just combine them:

junk=# select * from ordr;ordr_id | assigned_id | completion_id 
---------+-------------+---------------      1 |             |                    2 |           1 |
3|           1 |             2      4 |             |             2
 
(4 rows)

junk=# select * from employee;emp_id | last_name 
--------+-----------     1 | aaa     2 | bbb     3 | ccc
(3 rows)

junk=# SELECT o.ordr_id, a.last_name AS assigned, c.last_name AS completion
junk-#   FROM ordr AS o 
junk-#        LEFT JOIN employee AS a ON o.assigned_id = a.emp_id
junk-#        LEFT JOIN employee AS c ON o.completion_id = c.emp_id
junk-#   ORDER BY ordr_id;ordr_id | assigned | completion 
---------+----------+------------      1 |          |       2 | aaa      |       3 | aaa      | bbb      4 |          |
bbb
(4 rows)


-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "Follow peace with all men, and holiness, without which     no man shall see
theLord."       Hebrews 12:14