Re: left join syntax

"Haywood J'Bleauxmie" wrote:
>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?

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(dot)Elphick(at)lfix(dot)co(dot)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 the Lord." Hebrews 12:14