Re: Apparently I don't understand full outer joins.... - Mailing list 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;