pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIs it This Join Condition Do-Able?
7+ messages / 6 participants
[nested] [flat]
* Is it This Join Condition Do-Able?
@ 2005-08-17 16:54 Lane Van Ingen <lvaningen@esncc.com>
0 siblings, 2 replies; 7+ messages in thread
From: Lane Van Ingen @ 2005-08-17 16:54 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Is it This Join Condition Do-Able?
@ 2005-08-17 17:25 Dmitri Bichko <dbichko@aveopharma.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dmitri Bichko @ 2005-08-17 17:25 UTC (permalink / raw)
To: Lane Van Ingen <lvaningen@esncc.com>; pgsql-sql
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 confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking 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 computer
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Is it This Join Condition Do-Able?
@ 2005-08-17 17:43 Michael Fuhr <mike@fuhr.org>
parent: Lane Van Ingen <lvaningen@esncc.com>
1 sibling, 0 replies; 7+ messages in thread
From: Michael Fuhr @ 2005-08-17 17:43 UTC (permalink / raw)
To: Lane Van Ingen <lvaningen@esncc.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Is it This Join Condition Do-Able?
@ 2005-08-17 17:44 Jeremy Semeiks <jrs@farviolet.com>
parent: Lane Van Ingen <lvaningen@esncc.com>
1 sibling, 0 replies; 7+ messages in thread
From: Jeremy Semeiks @ 2005-08-17 17:44 UTC (permalink / raw)
To: Lane Van Ingen <lvaningen@esncc.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Is it This Join Condition Do-Able?
@ 2005-08-17 19:31 Mischa Sandberg <mischa.sandberg@telus.net>
parent: Dmitri Bichko <dbichko@aveopharma.com>
0 siblings, 1 reply; 7+ messages in thread
From: Mischa Sandberg @ 2005-08-17 19:31 UTC (permalink / raw)
To: Dmitri Bichko <dbichko@aveopharma.com>; +Cc: Lane Van Ingen <lvaningen@esncc.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Is it This Join Condition Do-Able?
@ 2005-08-17 19:57 Dmitri Bichko <dbichko@aveopharma.com>
0 siblings, 0 replies; 7+ messages in thread
From: Dmitri Bichko @ 2005-08-17 19:57 UTC (permalink / raw)
To: Mischa Sandberg <mischa.sandberg@telus.net>; +Cc: Lane Van Ingen <lvaningen@esncc.com>; 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 confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking 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 computer
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Is it This Join Condition Do-Able?
@ 2005-08-17 21:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mischa Sandberg <mischa.sandberg@telus.net>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2005-08-17 21:15 UTC (permalink / raw)
To: Mischa Sandberg <mischa.sandberg@telus.net>; +Cc: Dmitri Bichko <dbichko@aveopharma.com>; Lane Van Ingen <lvaningen@esncc.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-08-17 21:15 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-17 16:54 Is it This Join Condition Do-Able? Lane Van Ingen <lvaningen@esncc.com>
2005-08-17 17:43 ` Michael Fuhr <mike@fuhr.org>
2005-08-17 17:44 ` Jeremy Semeiks <jrs@farviolet.com>
2005-08-17 17:25 Re: Is it This Join Condition Do-Able? Dmitri Bichko <dbichko@aveopharma.com>
2005-08-17 19:31 ` Mischa Sandberg <mischa.sandberg@telus.net>
2005-08-17 21:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-17 19:57 Re: Is it This Join Condition Do-Able? Dmitri Bichko <dbichko@aveopharma.com>
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