Outer Join
I have tried, but I think that maybe I am confusing it. Here is an example of what I have so far. SELECT ebm.ebm_itemno, ebm.ebm_compitem, enb.enb_commcode, enb.enb_descrip, ebm_2.ebm_itemno, ebm_2.ebm_compitem, enb_2.enb_commcode, enb_2.enb_descrip, enb_2a.enb_commcode, enb_2a.enb_descrip FROM beidemo:informix.ebm ebm, beidemo:informix.enb enb, OUTER beidemo:informix.ebm ebm_child, OUTER beidemo:informix.ebm ebm_2, beidemo:informix.enb enb_2, beidemo:informix.enb enb_2a WHERE ebm.ebm_div = enb.enb_div AND ebm.ebm_compitem = enb.enb_itemno AND ebm.ebm_div = ebm_child.ebm_div AND ebm.ebm_compitem = ebm_child.ebm_itemno AND ebm_child.ebm_div = ebm_2.ebm_div AND ebm_child.ebm_compitem = ebm_2.ebm_itemno AND ebm_2.ebm_div = enb_2.enb_div AND ebm_2.ebm_compitem = enb_2.enb_itemno AND ebm_2.ebm_div = enb_2a.enb_div AND ebm_2.ebm_itemno = enb_2a.enb_itemno AND ebm.ebm_itemno="5VL696HPE" ORDER BY ebm.ebm_itemno ASC, ebm.ebm_compitem ASC, ebm_2.ebm_itemno ASC, ebm_2.ebm_compitem ASC I get "where clause results in a two sided outer join." I have tried to combine the joins but then I get and error about an "cartesian product." Libi Maniace BEI Sensors & Systems (501) 851-5417 lmaniace@beissd.com
L Maniace wrote in message <7eikmv$pfa$1@news.xmission.com>... You need to associate which tables to OUTER join on by using parentheses. Since enb_2 & enb_2a join to ebm_2, which in turn joins to ebm_child, I think this will work: FROM beidemo:informix.ebm ebm, beidemo:informix.enb enb, OUTER ( beidemo:informix.ebm ebm_child, OUTER ( beidemo:informix.ebm ebm_2, beidemo:informix.enb enb_2, beidemo:informix.enb enb_2a ) ) (indenting for readability only, and probably looks a mess if your viewer is too narrow) > >I have tried, but I think that maybe I am confusing it. >Here is an example of what I have so far. > >SELECT > ebm.ebm_itemno, ebm.ebm_compitem, > enb.enb_commcode, enb.enb_descrip, > ebm_2.ebm_itemno, ebm_2.ebm_compitem, > enb_2.enb_commcode, enb_2.enb_descrip, > enb_2a.enb_commcode, enb_2a.enb_descrip >FROM > beidemo:informix.ebm ebm, > beidemo:informix.enb enb, > OUTER beidemo:informix.ebm ebm_child, > OUTER beidemo:informix.ebm ebm_2, > beidemo:informix.enb enb_2, > beidemo:informix.enb enb_2a >WHERE > ebm.ebm_div = enb.enb_div AND > ebm.ebm_compitem = enb.enb_itemno AND > ebm.ebm_div = ebm_child.ebm_div AND > ebm.ebm_compitem = ebm_child.ebm_itemno AND > ebm_child.ebm_div = ebm_2.ebm_div AND > ebm_child.ebm_compitem = ebm_2.ebm_itemno AND > ebm_2.ebm_div = enb_2.enb_div AND > ebm_2.ebm_compitem = enb_2.enb_itemno AND > ebm_2.ebm_div = enb_2a.enb_div AND > ebm_2.ebm_itemno = enb_2a.enb_itemno AND > ebm.ebm_itemno="5VL696HPE" >ORDER BY > ebm.ebm_itemno ASC, > ebm.ebm_compitem ASC, > ebm_2.ebm_itemno ASC, > ebm_2.ebm_compitem ASC > >I get "where clause results in a two sided outer join." I have >tried to combine the joins but then I get and error about an >"cartesian product." > >Libi Maniace >BEI Sensors & Systems >(501) 851-5417 >lmaniace@beissd.com >
L Maniace wrote: > > I have tried, but I think that maybe I am confusing it. > Here is an example of what I have so far. > > SELECT > ebm.ebm_itemno, ebm.ebm_compitem, > enb.enb_commcode, enb.enb_descrip, > ebm_2.ebm_itemno, ebm_2.ebm_compitem, > enb_2.enb_commcode, enb_2.enb_descrip, > enb_2a.enb_commcode, enb_2a.enb_descrip > FROM > beidemo:informix.ebm ebm, > beidemo:informix.enb enb, > OUTER beidemo:informix.ebm ebm_child, > OUTER beidemo:informix.ebm ebm_2, > beidemo:informix.enb enb_2, > beidemo:informix.enb enb_2a > WHERE > ebm.ebm_div = enb.enb_div AND > ebm.ebm_compitem = enb.enb_itemno AND > ebm.ebm_div = ebm_child.ebm_div AND > ebm.ebm_compitem = ebm_child.ebm_itemno AND >{1} ebm_child.ebm_div = ebm_2.ebm_div AND >{2} ebm_child.ebm_compitem = ebm_2.ebm_itemno AND > ebm_2.ebm_div = enb_2.enb_div AND > ebm_2.ebm_compitem = enb_2.enb_itemno AND > ebm_2.ebm_div = enb_2a.enb_div AND > ebm_2.ebm_itemno = enb_2a.enb_itemno AND > ebm.ebm_itemno="5VL696HPE" > ORDER BY > ebm.ebm_itemno ASC, > ebm.ebm_compitem ASC, > ebm_2.ebm_itemno ASC, > ebm_2.ebm_compitem ASC > > I get "where clause results in a two sided outer join." I have > tried to combine the joins but then I get and error about an > "cartesian product." The lines which I've marked with the comments {1} and {2} are the source of the trouble, or at least, some of the trouble. You will need to dig out the appendix on complex outer joins which used to be part of the manuals (say 5.00 vintage -- I've not checked where it is in more recent versions). I think you're going to need some parentheses around some of your outer joined tables. Judging from the sets of joins you've written, the join diagram from the appendix would look a bit like: ebm ----- enb | ebm_child | enb_2a --- ebm_2 ----- enb_2 And if that's right, then you'd write the FROM clause as: FROM beidemo:informix.ebm ebm, beidemo:informix.enb enb, OUTER (beidemo:informix.ebm ebm_child, OUTER (beidemo:informix.ebm ebm_2, beidemo:informix.enb enb_2, beidemo:informix.enb enb_2a)) It's a bit difficult to tell what this might mean; there are 3 joins to the same table, and two levels of outer self-join. Tricky stuff. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>