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
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