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

["Thomas F.O'Connell" <tfo@xxxxxxxxxxxx>]

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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

              http://archives.postgresql.org