pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Oliver Elphick <olly@lfix.co.uk>
To: Haywood J'Bleauxmie <hj@fc3.outerscape.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: left join syntax
Date: Tue, 08 May 2001 12:29:08 +0100
Message-ID: <200105081129.f48BT8iW006285@linda.lfix.co.uk> (raw)
In-Reply-To: <hj@fc3.outerscape.net> <fc.009863ef00002846009863ef00002846.284b@fc3.outerscape.net>

"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 





view thread (2+ messages)

Message-ID: <200105081129.f48BT8iW006285@linda.lfix.co.uk>
Permalink:  ../200105081129.f48BT8iW006285@linda.lfix.co.uk/
Also on:    postgresql.org/message-id/200105081129.f48BT8iW006285@linda.lfix.co.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: olly@lfix.co.uk, hj@fc3.outerscape.net
  Subject: Re: left join syntax
  In-Reply-To: <200105081129.f48BT8iW006285@linda.lfix.co.uk>
  References: <200105081129.f48BT8iW006285@linda.lfix.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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