From: Mischa Sandberg <mischa.sandberg@telus.net>
To: Dmitri Bichko <dbichko@aveopharma.com>
Cc: Lane Van Ingen <lvaningen@esncc.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Is it This Join Condition Do-Able?
Date: Wed, 17 Aug 2005 12:31:16 -0700
Message-ID: <1124307076.4303908468d72@webmail.telus.net> (raw)
In-Reply-To: <F18A6F7CF1661F46920F2CF713122FED46CC69@mail.aveo.aveopharma.com>
References: <F18A6F7CF1661F46920F2CF713122FED46CC69@mail.aveo.aveopharma.com>
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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mischa.sandberg@telus.net, dbichko@aveopharma.com, lvaningen@esncc.com
Subject: Re: Is it This Join Condition Do-Able?
In-Reply-To: <1124307076.4303908468d72@webmail.telus.net>
* 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