Re: Trouble with strange OUTER JOIN syntax

> > Having examined the statements in more detail, it seems to me like
> > some kind of "multijoin" is required here:
> >
> > * outer-joining more than one table to a primary table:
> > SELECT ... FROM tabmain, OUTER tab2, OUTER tab3
> >
> > Here, I'll guess that both tab2 and tab3 are being outer-joined
> > to tabmain. Therefore tab2 and tab3 columns are allowed to
> > be null, whereas tabmain column's are not.
> >
> > * outer-joining one (or more than one) table to a cartesian
> > product of other tables:
> > SELECT ... FROM tab1, tab2, OUTER tab3
> > SELECT ... FROM tab1, tab2, OUTER (tab3, tab4)
> > SELECT ... FROM tab1, tab2, OUTER tab3, OUTER tab4
> >
> > In the first example, tab3 is being joind to the cartesian product
> > (inner join) of both tab1 and tab2. Therefore tab1 x tab2 is not
> > allowed to be null, whereas tab3 is allowed to be.
> >
> > The next examples seem to generalize this: two tables (tab3 and tab4)
> > are being outer-joined to existing cartesian product tab1 x tab2.
> > I'm not sure what the difference may be between:
> > OUTER (tab3, tab4)
> > and
> > OUTER tab3, OUTER tab4.
> >
> > If PostgreSQL doesn't support this feature (yet?), I'll have to simulate
> > it programatically. I just hoped to avoid the trouble of doing so,
> > because the program I'm porting contains a lot of such "multijoins".
>
> How 'bout:
>
> SELECT * FROM foo LEFT JOIN bar ON foo.foo_id = bar.foo_id
> LEFT JOIN baz on bar.id = baz.bar_id;
>
> simple example output:
>
> foo_id | data | foo_id | bar_id | data | baz_id | bar_id | data
> --------+------+--------+--------+---------+--------+--------+-------------
> 1 | one | 1 | 1 | one-one | 1 | 1 | one-one-one
> 1 | one | 1 | 1 | one-one | 2 | 1 | one-one-two
> 1 | one | 1 | 2 | one-two | | |
> 2 | two | | | | | |
> (4 rows)
>
> "baz" has no candidate with "bar_id" = 2, and "bar" has no candidate where
> "foo_id" = 2. But it's not a product, as baz has a {3,3,'three-three-three'}
> tuple that won't join. You have to specify a join condition, or you get a
> product.
Yes, chaining the outer join(s) did the trick for me here!

> Eric G. Miller <egm2(at)jps(dot)net>

Many thanks,

-Farid.

--
Farid Hajji -- Unix Systems and Network Admin | Phone: +49-2131-67-555
Broicherdorfstr. 83, D-41564 Kaarst, Germany | farid(dot)hajji(at)ob(dot)kamp(dot)net
- - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - -
One OS To Rule Them All And In The Darkness Bind Them... --Bill Gates.