RE: Where Not In with unexpected results

This seems like a similar post from a while back. I'd be willing to bet that you have null rows in table2. Add 'and colb is not null' to your sub-query and I bet it works. } -----Original Message----- } From: danielk@vfc.com [SMTP:danielk@vfc.com] } Sent: Thursday, December 11, 1997 10:34 AM } To: informix-list@rmy.emory.edu } Subject: Where Not In with unexpected results } } Using the sql statement and not returning any rows. } } select distinct col1 from table1 } where col2 = 'ValueB' } and col1 not in (select distinct colb from table2); } } This query returns no rows. } } Change the NOT IN to IN and results are two rows returned. } } select distinct colb from table2; } Results: 2 rows with colb values of 'BALL' and 'ORANGE) } select distinct col1 from table1 where col2 = 'ValueB'; } Results: 5 Rows with col1 values of } 'BALL','ORANGE','TRIANGLE','HAT','CAT' } } Is there a problem with the SQL? I (and others) would expect the } results } to be the three rows with col1 values of 'TRIANGL', 'HAT', and 'CAT'. } } -------------------==== Posted via Deja News } ====----------------------- } http://www.dejanews.com/ Search, Read, Post to Usenet