From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Ben <bench(at)silentmedia(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Apparently I don't understand full outer joins.... |
Date: | 2005-01-25 20:59:11 |
Message-ID: | 20050125125406.J33583@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | tmp | 2005-01-25 21:03:58 | Re: Recursive queries |
Previous Message | Richard Poole | 2005-01-25 20:53:07 | Re: Apparently I don't understand full outer joins.... |