Re: Trouble with strange OUTER JOIN syntax - Mailing list pgsql-general

> > 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@jps.net>

Many thanks,

-Farid.

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