Re: left join syntax

"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