outer join instead of not in

Thanks for any insights... I thought I read once that you could use an outer join instead of a "not in subquery". I'm trying this, however when I try to filter the result columns for the null values, my filter is affecting the tables before the join (at least I think so) rather than after. I suspect this because when I remove the "p2.base_product is NULL" from the where clause I can see one row which comes back with a null in the column. If I add the "x is null" into the where clause, I now get rows back (instead of the 1 I was expecting.) Any ideas? Here is the SQL: set isolation to dirty read; select p1.install_no ,p1.base_product ,p1.status_code ,p1.status_cd ,p1.maint_type ,p2.base_product from stminsprd p1, -- select all the sites who's products are not ML stminsthd h, -- OUTER stminsprd p2 -- select all the sites who's products are MLand outer join with p1 the outer join colum where null will be those sites whodon't have matlab at all where p1.base_product != 'ML' and p1.end_date > '05/01/2000' and p1.base_product in ('CP','MA') and p1.status_code not in ('RT','LT','STO','TI','TRL') and p1.maint_type in ('COMP','FXF','MMC','TCP') and p1.install_no = p2.install_no and p2.base_product = 'ML' and p2.base_product IS NULL -- only show the outer join columns and p1.install_no = h.install_no and h.status_code = 'A' and h.install_no between 151505 and 152000 order by p1.install_no

Ben Draper wrote: > Thanks for any insights... > > I thought I read once that you could use an outer join instead of a "not in > subquery". > > I'm trying this, however when I try to filter the result columns for the > null values, my filter is affecting the tables before the join (at least I > think so) rather than after. I suspect this because when I remove the > "p2.base_product is NULL" from the where clause I can see one row which > comes back with a null in the column. If I add the "x is null" into the > where clause, I now get rows back (instead of the 1 I was expecting.) > > Any ideas? I don't know where you read about using OUTER in place of NOT IN, but I suspect it was either faulty or using SQL-92 OUTER joins and not Informix OUTER joins. If you really want to use OUTER (and I can't immediately think of a reason I'd want to), then you probably need to put a more-or-less unfiltered version of the results into a temp table, and then filter the results. Basically, any filter which operates on the outer table (p2) should be removed from the select into temp. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN #include <disclaimer.h>

Ben Draper wrote: > > Thanks for any insights... > > I thought I read once that you could use an outer join instead of a "not in > subquery". > > I'm trying this, however when I try to filter the result columns for the > null values, my filter is affecting the tables before the join (at least I > think so) rather than after. I suspect this because when I remove the > "p2.base_product is NULL" from the where clause I can see one row which > comes back with a null in the column. If I add the "x is null" into the > where clause, I now get rows back (instead of the 1 I was expecting.) > > Any ideas? You either have to use the newer ANSI '92 syntax for outer joins and place the outer join conditions into the JOIN section if you have 7.3x or 9.2x (see the release notes for the SQL manual for details of the syntax) or SELECT .... TO TEMP... without the "IS NULL" filter then elect from the temp table using the "IS NULL" condition if you have an earlier version that does not support ANSI '92 syntax or do not want to use it. Art S. Kagel > Here is the SQL: > set isolation to dirty read; > select p1.install_no > ,p1.base_product > ,p1.status_code > ,p1.status_cd > ,p1.maint_type > ,p2.base_product > from > stminsprd p1, -- select all the sites who's products are not ML > stminsthd h, -- > OUTER stminsprd p2 -- select all the sites who's products are MLand > outer join with p1 the outer join colum where null will be those sites who> don't have matlab at all > where > p1.base_product != 'ML' > and p1.end_date > '05/01/2000' > and p1.base_product in ('CP','MA') > and p1.status_code not in ('RT','LT','STO','TI','TRL') > and p1.maint_type in ('COMP','FXF','MMC','TCP') > and p1.install_no = p2.install_no > and p2.base_product = 'ML' > and p2.base_product IS NULL -- only show the outer join columns > and p1.install_no = h.install_no > and h.status_code = 'A' > and h.install_no between 151505 and 152000 > order by p1.install_no