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 15:57:37 -0400
Message-ID: <F18A6F7CF1661F46920F2CF713122FED46CC6D@mail.aveo.aveopharma.com>
Thread-Topic: [SQL] Is it This Join Condition Do-Able? 
Thread-Index: AcWjYo8Iah2T9wH4QvKOCnZky1AA6QAAPgKQ
From: "Dmitri Bichko" <dbichko@aveopharma.com>
To: "Mischa Sandberg" <mischa.sandberg@telus.net>
Cc: "Lane Van Ingen" <lvaningen@esncc.com>,
	<pgsql-sql@postgresql.org>

I don't see what the problem is.

Did you mean to insert (3,'C3') into table c, rather than b?

Dmitri

> -----Original Message-----
> From: Mischa Sandberg [mailto:mischa.sandberg@telus.net]=20
> Sent: Wednesday, August 17, 2005 3:31 PM
> To: Dmitri Bichko
> Cc: Lane Van Ingen; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Is it This Join Condition Do-Able?=20
>=20
>=20
> Quoting Dmitri Bichko <dbichko@aveopharma.com>:
>=20
> > How about:
> >=20
> > SELECT a.keyfld, a.foo1, b.foo2, c.foo3
> > FROM a
> > LEFT JOIN b USING(keyfld)
> > LEFT JOIN c USING(keyfld)
>=20
> ((( See response at end )))
>=20
> > > -----Original Message-----=20
> [mailto:pgsql-sql-owner@postgresql.org]=20
> > > On Behalf Of Lane Van
> > Ingen
> > > Sent: Wednesday, August 17, 2005 12:55 PM
> > > Subject: [SQL] Is it This Join Condition Do-Able?
> > >=20
> > > 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
> > >=20
> > > 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;=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
> Having a bit of uncertainty of how LEFT JOIN associates, I tried the
> following test (psql -qe), with (to me) highly surprising results.
> Anyone care to comment on the third row of output?
>=20
> select version();
>                                       version
> --------------------------------------------------------------
> ---------------------
> PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3
> (SuSE Linux)
>=20
> create temp table a(keyf int, val text);
> create temp table b(keyf int, val text);
> create temp table c(keyf int, val text);
> insert into a values(1, 'A1');
> insert into a values(2, 'A2');
> insert into a values(3, 'A3');
> insert into a values(4, 'A4');
> insert into b values(1, 'B1');
> insert into b values(2, 'B2');
> insert into c values(2, 'C2');
> insert into b values(3, 'C3');
> select keyf, a.val as aval,
>         coalesce(b.val,'Bxx') as bval,
>         coalesce(c.val,'Cxx') as cval
> from a left join b using(keyf) left join c using (keyf);
> keyf aval bval cval
> ---- ---- ---- ----
>    1 A1   B1   Cxx
>    2 A2   B2   C2
>    3 A3   C3   Cxx
>    4 A4   Bxx  Cxx
>=20
>=20
>=20
>=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