"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