Re: Apparently I don't understand full outer joins.... - Mailing list pgsql-general

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