Darn query causing me grief

[brettgab]

I have two tables, an I'm trying to list all the items from I_ToDecide.stock_code which do not have a corresponding value in I_Stock_Colours.colour_stock_code.

When I do a query using IN (SELECT...), the correct results are retured, but when I do a NOT IN (SELECT...), no values are returned.

The table fields are the same type. I can't understand the problem.

Here is the query.

SELECT * FROM I_ToDecide TD
WHERE TD.stock_code NOT IN (SELECT colour_stock_code FROM I_Stock_Colours)

Like I said, if I remove the NOT, it returns the correct results, but including the NOT (which is what I want to get the correct results) returns an empty recordset.

How can this BE ? I would have thought that if you had say 100 records in I_ToDecide and the 'IN' query returned 30 records, then NOT IN would have to return 70 records.

 

[tlbroadbent]

Do the records that don't match happen to have NULL value in the stock_code column. If so, SQL will not find them because Null means 'no value assigned' so SQL cannot determine that the value Is Not in the other set.

Use a Left Outer Join query instead of Not In a subquery.

Select *
FROM I_ToDecide TD
Left Join I_Stock_Colours I
On TD.stock_code=I.colour_stock_code
Where I.colour_stock_code Is Null Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.