Thread: 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. 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
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. Does this example do what you want? CREATE TABLE a (keyfld integer, foo1 text); CREATE TABLE b (keyfld integer, foo2 text); CREATE TABLE c (keyfld integer, foo3 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 (4, 'b4'); INSERT INTO c VALUES (2, 'c2'); INSERT INTO c VALUES (4, 'c4'); SELECT a.keyfld, a.foo1, b.foo2, c.foo3 FROM a LEFT OUTER JOIN b USING (keyfld) LEFT OUTER JOIN c USING (keyfld);keyfld | foo1 | foo2 | foo3 --------+------+------+------ 1 | a1 | b1 | 2 | a2 | | c2 3 | a3 | | 4 | a4 | b4 | c4 (4 rows) -- Michael Fuhr
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 > [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? > > > 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 > > > > ---------------------------(end of > 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 > 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
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
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
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
Mischa Sandberg <mischa.sandberg@telus.net> writes:
> Anyone care to comment on the third row of output?
I think you mistyped the last INSERT:
> insert into c values(2, 'C2');
> insert into b values(3, 'C3');
I suppose you meant insert into c ...
regards, tom lane
The Subject says it all. (author beats a hasty retreat). Quoting Dmitri Bichko <dbichko@aveopharma.com>: > I don't see what the problem is. > Did you mean to insert (3,'C3') into table c, rather than b? > > 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