CODEWITHSUNDEEP
sql
user3673417
user3673417

Reputation: 189

NOT IN Sub Query

I am trying to exclude records where they are returned as part of a sub query. Return a list of all 'Items' which have a location = 'Q', a positive quantity on hand and there have been no transactions in the last 14 days. When i run the two queries separately they both work fine but when i run them together as below no records are returned although there are definitely matching records.

SELECT l.item AS 'Item'
    FROM itemloc l
    WHERE l.loc = 'Q' AND l.qty_on_hand > 0
    AND l.item NOT IN 
    (SELECT t.item
    FROM  transaction t
    WHERE t.RecordDate > DATEADD(DAY, -14, GETDATE())
    GROUP BY t.item)

Upvotes: 0

Views: 1287

Answers (2)

Dmitry
Dmitry

Reputation: 340

edit the item column, make it not nullable, this is where your correct sql query breaks. or use joins instead

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269593

Instead of not in, use not exists (or a left join and comparison to NULL). So, try this:

WHERE l.loc = 'Q' AND l.qty_on_hand > 0 AND
      NOT EXISTS (SELECT 1 
                  FROM  transaction t
                  WHERE t.RecordDate > DATEADD(DAY, -14, GETDATE()) and t.item = l.item
                 )

The problem is (probably) that t.item takes on a NULL value. In that case, the NOT IN returns "unknown" or "false". And, "unknown" is treated as false. So, when the subquery has a NULL value, the expression can never return "true".

You can also fix this by adding a where clause in the subquery. However, I find that the semantics of NOT IN are unintuitive to just about everyone. For that reason, I recommend using NOT EXISTS instead.

Also, you don't need a group by the subquery. That is superfluous.

Upvotes: 1

Related Questions