Re: Trouble with strange OUTER JOIN syntax
От | Farid Hajji |
---|---|
Тема | Re: Trouble with strange OUTER JOIN syntax |
Дата | |
Msg-id | 200105280018.f4S0Ic603555@suse-1.meta.net обсуждение исходный текст |
Ответ на | Trouble with strange OUTER JOIN syntax (Farid Hajji <farid.hajji@ob.kamp.net>) |
Список | 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.
В списке pgsql-general по дате отправления: