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 106EA528DB for ; Wed, 17 Aug 2005 14:23:43 -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 62487-07 for ; Wed, 17 Aug 2005 17:23:41 +0000 (GMT) Received: from mail.aveo.aveopharma.com (67.109.105.227.ptr.us.xo.net [67.109.105.227]) by svr1.postgresql.org (Postfix) with SMTP id 1B1A4528DA for ; Wed, 17 Aug 2005 14:23:40 -0300 (ADT) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Re: Is it This Join Condition Do-Able? Date: Wed, 17 Aug 2005 13:25:53 -0400 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] Is it This Join Condition Do-Able? Thread-Index: AcWjUE1Ck3zY8CCTTp6KMsVWz2P7UAAAEBTg From: "Dmitri Bichko" To: "Lane Van Ingen" , X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.351 required=5 tests=[AWL=-0.285, FORGED_RCVD_HELO=0.05, HOT_NASTY=0.586] X-Spam-Level: X-Archive-Number: 200508/125 X-Sequence-Number: 22353 How about: SELECT a.keyfld, a.foo1, b.foo2, c.foo3 FROM a LEFT JOIN b USING(keyfld) LEFT JOIN c USING(keyfld) Dmitri > -----Original Message----- > From: pgsql-sql-owner@postgresql.org=20 > [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Lane Van Ingen > Sent: Wednesday, August 17, 2005 12:55 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Is it This Join Condition Do-Able?=20 >=20 >=20 > Given three tables: a, b, c ; each consist of a 'keyfld' and=20 > a field called > 'foo': > tbl a tbl b tbl c > --------- --------- --------- > a.keyfld b.keyfld c.keyfld > a.foo1 b.foo2 c.foo3 >=20 > I want to always return all of tbl a; and I want to return=20 > b.foo2 and c.foo3 if they can be joined to based on keyfld.a;=20 > I know that it will involve a LEFT OUTER JOIN on table a, but=20 > have not seen any examples of joins like this on 3 or more tables. >=20 > select a.keyfld, a.foo1, b.foo2, c.foo3 > from a, b, c > where a.keyfld =3D > and a.keyfld =3D b.keyfld > and a.keyfld =3D c.keyfld; >=20 > 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 >=20 >=20 >=20 > ---------------------------(end of=20 > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >=20 The information transmitted is intended only for the person or entity to = which it is addressed and may contain confidential and/or privileged mate= rial. Any review, retransmission, dissemination or other use of, or takin= g of any action in reliance upon, this information by persons or entities= other than the intended recipient is prohibited. If you received this in= error, please contact the sender and delete the material from any comput= er