pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
left join syntax
2+ messages / 2 participants
[nested] [flat]

* left join syntax
@ 2001-05-08 07:36 Haywood J'Bleauxmie <hj@fc3.outerscape.net>
  2001-05-08 11:29 ` Re: left join syntax Oliver Elphick <olly@lfix.co.uk>
  0 siblings, 1 reply; 2+ messages in thread

From: Haywood J'Bleauxmie @ 2001-05-08 07:36 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: left join syntax
  2001-05-08 07:36 left join syntax Haywood J'Bleauxmie <hj@fc3.outerscape.net>
@ 2001-05-08 11:29 ` Oliver Elphick <olly@lfix.co.uk>
  0 siblings, 0 replies; 2+ messages in thread

From: Oliver Elphick @ 2001-05-08 11:29 UTC (permalink / raw)
  To: Haywood J'Bleauxmie <hj@fc3.outerscape.net>; +Cc: pgsql-sql

"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.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 the Lord."       Hebrews 12:14 





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-05-08 11:29 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-08 07:36 left join syntax Haywood J'Bleauxmie <hj@fc3.outerscape.net>
2001-05-08 11:29 ` Oliver Elphick <olly@lfix.co.uk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox