Thread: Apparently I don't understand full outer joins....

Apparently I don't understand full outer joins....

From
Ben
Date:
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




Re: Apparently I don't understand full outer joins....

From
Thomas F.O'Connell
Date:
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


Re: Apparently I don't understand full outer joins....

From
Richard Poole
Date:
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

Re: Apparently I don't understand full outer joins....

From
Stephan Szabo
Date:
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;


Re: Apparently I don't understand full outer joins....

From
Ben
Date:
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;
>
>



Re: Apparently I don't understand full outer joins....

From
"Lee Harr"
Date:
>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/