Thread: Apparently I don't understand full outer joins....
I run this:
select
coalesce(a.n,0) as a,
coalesce(b.n,0) as b,
coalesce(a.s,b.s) as s
from
( select 1 as n, 0 as s) a full outer join
( select 2 as n, 1 as s) b
on
a.s = b.s
... and get this:
a | b | s
---+---+---
1 | 0 | 0
0 | 2 | 1
(2 rows)
Perfect! Now, I try to extend my understanding to 3 subselects:
select
coalesce(a.n,0) as a,
coalesce(b.n,0) as b,
coalesce(c.n,0) as c,
coalesce(a.s,b.s,c.s) as s
from
( select 1 as n, 0 as s) a full outer join
( select 1 as n, 1 as s) b full outer join
( select 2 as n, 2 as s) c
on
a.s = b.s and
b.s = c.s
.... and get a syntax error at the end of my query. Apparently what I'm
trying to do doesn't make sense?
Oh, this is on version 7.4, if that makes a difference.
---
Ben Chobot
Senior Technical Specialist, Washington Mutual
206-461-4005
Your second example is breaking the syntax of from_item ( see <http://www.postgresql.org/docs/7.4/static/sql-select.html> ). Your join_condition has to be applied to the two from_items associated by join_type. I don't think multiple join_conditions can be applied sequentially the way you're trying to do it. You could probably create a nested structure, though. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jan 25, 2005, at 2:29 PM, Ben wrote: > I run this: > > select > coalesce(a.n,0) as a, > coalesce(b.n,0) as b, > coalesce(a.s,b.s) as s > from > ( select 1 as n, 0 as s) a full outer join > ( select 2 as n, 1 as s) b > on > a.s = b.s > > ... and get this: > > a | b | s > ---+---+--- > 1 | 0 | 0 > 0 | 2 | 1 > (2 rows) > > > Perfect! Now, I try to extend my understanding to 3 subselects: > > select > coalesce(a.n,0) as a, > coalesce(b.n,0) as b, > coalesce(c.n,0) as c, > coalesce(a.s,b.s,c.s) as s > from > ( select 1 as n, 0 as s) a full outer join > ( select 1 as n, 1 as s) b full outer join > ( select 2 as n, 2 as s) c > on > a.s = b.s and > b.s = c.s > > > .... and get a syntax error at the end of my query. Apparently what I'm > trying to do doesn't make sense? > > Oh, this is on version 7.4, if that makes a difference. > > > --- > Ben Chobot > Senior Technical Specialist, Washington Mutual > 206-461-4005 > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
On Tue, Jan 25, 2005 at 12:29:07PM -0800, Ben wrote:
> select
> coalesce(a.n,0) as a,
> coalesce(b.n,0) as b,
> coalesce(c.n,0) as c,
> coalesce(a.s,b.s,c.s) as s
> from
> ( select 1 as n, 0 as s) a full outer join
> ( select 1 as n, 1 as s) b full outer join
> ( select 2 as n, 2 as s) c
> on
> a.s = b.s and
> b.s = c.s
>
>
> .... and get a syntax error at the end of my query. Apparently what I'm
> trying to do doesn't make sense?
The ON clauses have to be attached directly to the outer joins. So you
probably mean
select
coalesce(a.n,0) as a,
coalesce(b.n,0) as b,
coalesce(c.n,0) as c,
coalesce(a.s,b.s,c.s) as s
from
( select 1 as n, 0 as s) a
full outer join
( select 1 as n, 1 as s) b
on a.s = b.s
full outer join
(select 2 as n, 2 as s) c
on b.s = c.s;
Richard
On Tue, 25 Jan 2005, Ben wrote:
> I run this:
>
> select
> coalesce(a.n,0) as a,
> coalesce(b.n,0) as b,
> coalesce(a.s,b.s) as s
> from
> ( select 1 as n, 0 as s) a full outer join
> ( select 2 as n, 1 as s) b
> on
> a.s = b.s
>
> ... and get this:
>
> a | b | s
> ---+---+---
> 1 | 0 | 0
> 0 | 2 | 1
> (2 rows)
>
>
> Perfect! Now, I try to extend my understanding to 3 subselects:
>
> select
> coalesce(a.n,0) as a,
> coalesce(b.n,0) as b,
> coalesce(c.n,0) as c,
> coalesce(a.s,b.s,c.s) as s
> from
> ( select 1 as n, 0 as s) a full outer join
> ( select 1 as n, 1 as s) b full outer join
> ( select 2 as n, 2 as s) c
> on
> a.s = b.s and
> b.s = c.s
>
>
> .... and get a syntax error at the end of my query. Apparently what I'm
> trying to do doesn't make sense?
Each outer join gets an on clause. You might want something like:
select
coalesce(a.n,0) as a,
coalesce(b.n,0) as b,
coalesce(c.n,0) as c,
coalesce(a.s,b.s,c.s) as s
from
( select 1 as n, 0 as s) a full outer join
( select 1 as n, 1 as s) b on (a.s=b.s) full outer join
( select 2 as n, 2 as s) c on b.s = c.s;
Thanks guys, this works great. On Tue, 25 Jan 2005, Stephan Szabo wrote: > > Each outer join gets an on clause. You might want something like: > select > coalesce(a.n,0) as a, > coalesce(b.n,0) as b, > coalesce(c.n,0) as c, > coalesce(a.s,b.s,c.s) as s > from > ( select 1 as n, 0 as s) a full outer join > ( select 1 as n, 1 as s) b on (a.s=b.s) full outer join > ( select 2 as n, 2 as s) c on b.s = c.s; > >
>select
> coalesce(a.n,0) as a,
> coalesce(b.n,0) as b,
> coalesce(c.n,0) as c,
> coalesce(a.s,b.s,c.s) as s
>from
> ( select 1 as n, 0 as s) a full outer join
> ( select 1 as n, 1 as s) b full outer join
> ( select 2 as n, 2 as s) c
>on
> a.s = b.s and
> b.s = c.s
>
>
>.... and get a syntax error at the end of my query. Apparently what I'm
>trying to do doesn't make sense?
>
Maybe this is what you want...
select
coalesce(a.n,0) as a,
coalesce(b.n,0) as b,
coalesce(c.n,0) as c,
coalesce(a.s,b.s,c.s) as s
from
( select 1 as n, 0 as s) a full outer join
( select 1 as n, 1 as s) b
on a.s = b.s
full outer join
( select 2 as n, 2 as s) c
on
b.s = c.s
a | b | c | s
---+---+---+---
1 | 0 | 0 | 0
0 | 1 | 0 | 1
0 | 0 | 2 | 2
_________________________________________________________________
FREE pop-up blocking with the new MSN Toolbar - get it now!
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/