X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id A03F052BE6
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 17 Aug 2005 14:45:02 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 66557-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 17 Aug 2005 17:44:58 +0000 (GMT)
Received: from farviolet.com (farviolet.com [64.81.61.206])
	by svr1.postgresql.org (Postfix) with ESMTP id 0599252BDF
	for <pgsql-sql@postgresql.org>; Wed, 17 Aug 2005 14:44:53 -0300 (ADT)
Received: from farviolet.com (localhost [127.0.0.1])
	by farviolet.com (8.12.11/8.12.10) with ESMTP id j7HHioZE021449;
	Wed, 17 Aug 2005 10:44:50 -0700
Received: (from jrs@localhost)
	by farviolet.com (8.12.11/8.12.11/Submit) id j7HHioFK021448;
	Wed, 17 Aug 2005 10:44:50 -0700
Date: Wed, 17 Aug 2005 10:44:50 -0700
From: Jeremy Semeiks <jrs@farviolet.com>
To: Lane Van Ingen <lvaningen@esncc.com>, pgsql-sql@postgresql.org
Subject: Re: Is it This Join Condition Do-Able?
Message-ID: <20050817174450.GU28158@farviolet.farviolet.com>
References: <EKEMKEFLOMKDDLIALABIAEHJCBAA.lvaningen@esncc.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <EKEMKEFLOMKDDLIALABIAEHJCBAA.lvaningen@esncc.com>
User-Agent: Mutt/1.5.6i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.586 required=5 tests=[HOT_NASTY=0.586]
X-Spam-Level: 
X-Archive-Number: 200508/127
X-Sequence-Number: 22355

On Wed, Aug 17, 2005 at 12:54:50PM -0400, Lane Van Ingen wrote:
> Given three tables: a, b, c ; each consist of a 'keyfld' and a field called
> 'foo':
>      tbl a       tbl b         tbl c
>    ---------   ---------     ---------
>    a.keyfld    b.keyfld       c.keyfld
>    a.foo1      b.foo2         c.foo3
> 
> I want to always return all of tbl a; and I want to return b.foo2 and c.foo3
> if
> they can be joined to based on keyfld.a; I know that it will involve a LEFT
> OUTER
> JOIN on table a, but have not seen any examples of joins like this on 3 or
> more
> tables.
> 
> select a.keyfld, a.foo1, b.foo2, c.foo3
> from a, b, c
> where a.keyfld = <some value>
> and   a.keyfld = b.keyfld
> and   a.keyfld = c.keyfld;
> 
> Results could look like this:
>   a.keyfld  a.foo1   b.foo2  c.foo3
>     xxxx     xxxx     xxxx    (null)
>     xxxx     xxxx    (null)    xxxx
>     xxxx     xxxx    (null)   (null)
>     xxxx     xxxx     xxxx     xxxx

Just use two left joins:

select a.keyfld, a.foo1, b.foo2, c.foo3
from a
left join b on a.keyfld = b.keyfld
left join c on a.keyfld = c.keyfld
where a.keyfld = <some value>;

HTH,
Jeremy