"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                                [EMAIL PROTECTED]
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 



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to