Re: JOIN issue

On Dec 28, 2007 3:39 PM, Kennedy, Randy <RKennedy@scottsdaleaz.gov> wrote: > I am trying to do a join in an SPL routine and it is not returning what I am > wanting so any help is appreciated: > Informix 9.4FC3 on HPUX > > I have a view created that joins 3 tables and that returns a 27k row view. > > Within my SPL, I want to join this view to 3 tables, ensuring I get a row > when any of the 3 tables meets a criteria (but all 3 may not). > > I have it currently as "... FROM view, OUTER tab1, OUTER tab2, OUTER tab3 > WHERE v.1 = tab1.1 and v.2 = tab1.2 and v.1 = tab2.1 and v.2 = tab2.2 etc". > > But instead of getting a row from View when any of the 3 tables has a match, > I get EVERY row from view and it dupes tab1,2,3 info to fit. > > It is my understanding that if I use an inner join, if any of the 3 tables > doesn't have a matching row, the entire row won't be returned and that is > what I am trying to avoid. > > Example: > > View rows: > 1 a some value > 1 b some value > 2 a some value > ... > 55 z some value > > > tab1, tab2, and tab3 have identical columns: > 1 a data > 1 b data > 2 c data > ... > > > I need the return from my SPL to be > view field1, view field2, tab1 field 3, tab2 field 3, tab3 field 3, > calculated field of (tab1-tab2-tab3 field3's), some more columns. > > I want to ensure that I will get the row even if tab1 2 OR 3 doesn't have a > matching row (I use NVL(field,0) so arithmetic still works). > > What I end up getting is every view rows data and tab 1,2, & 3 rows repeated > even though there are NO match in any of the 3 tables. > > What do I need to do to ensure getting every row I need, but not get > erroneous duplicates? The Informix-style OUTER join is a wondrous and weird beastie - and extremely hard to explain properly. If you have a two-table OUTER join such as: SELECT * FROM Tab1, OUTER Tab2 WHERE Tab1.SomeCol = Tab2.SomeCol AND Tab2.OtherCol = 12 then Tab1 is the dominant table and Tab2 is the subordinate table. All the rows in the dominant table will be returned. Where there is one or more rows in the subordinate table matching both the join and filter conditions, then those rows will be returned in the result set. Where there are no rows in the subordinate table matching both the join and filter conditions, then the row from tab1 will be returned with nulls in place of the values from tab2. Note that if I'd added a filter such as 'AND Tab1.AnotherCol = 42', then only the rows from tab1 that match this criterion would be returned - but every row in tab1 that matches the criterion would be included. In your example, you have three two-table OUTER joins; these rules apply to each of the three outer joins. You said: > Within my SPL, I want to join this view to 3 tables, ensuring I get a row > when any of the 3 tables meets a criteria (but all 3 may not). Does that mean you want a row from the view to appear when it matches a row in table 1, or when it matches a row in table 2, or when it matches a row in table 3, or any combination of these, but not when it does not match any row in any of the three auxilliary tables. One way to simplify this is to select with the OUTER joins into an intermediate temporary table and then apply filters to the temporary table. In your example, the filter would eliminate those rows from the temp table for which none of the auxilliary tables matches the row in the view. Alternatively, you could use a 7-way UNION: SELECT * FROM v, tab1, tab2, tab3 WHERE ... UNION SELECT * FROM v, tab1, tab2, OUTER tab3 WHERE ... UNION...two more single OUTER alternatives... UNION SELECT * FROM v, tab1, OUTER tab2, OUTER tab3 WHERE UNION..two more double OUTER alternatives... Using UNION you get duplicate elimination, which would prevent duplication of the rows where the inner join is satisfied, for example. Consider using ISO (ANSI) style OUTER joins - they have different (but standard) rules for which rows are preserved. Whether it makes it significantly easier is debatable. The fact that you're using a view as the dominant table is not an issue. I've seen worse. In particular, one system I worked on many years ago specialized in outer self-joins (with views being used extensively). Those are confusing! -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/ NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.