Re: Is it This Join Condition Do-Able? - Mailing list pgsql-sql

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]
> 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?
>
>
> Quoting Dmitri Bichko <dbichko@aveopharma.com>:
>
> > How about:
> >
> > SELECT a.keyfld, a.foo1, b.foo2, c.foo3
> > FROM a
> > LEFT JOIN b USING(keyfld)
> > LEFT JOIN c USING(keyfld)
>
> ((( See response at end )))
>
> > > -----Original Message-----
> [mailto:pgsql-sql-owner@postgresql.org]
> > > On Behalf Of Lane Van
> > Ingen
> > > Sent: Wednesday, August 17, 2005 12:55 PM
> > > Subject: [SQL] Is it This Join Condition Do-Able?
> > >
> > > 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.
> ...
>
> 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?
>
> select version();
>                                       version
> --------------------------------------------------------------
> ---------------------
> PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3
> (SuSE Linux)
>
> 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
>
>
>
>
The information transmitted is intended only for the person or entity to which it is addressed and may contain
confidentialand/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any
actionin reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you
receivedthis in error, please contact the sender and delete the material from any computer