Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

SQL Server - NOT IN Help

i need help with NOT IN, please.

table a - field name is itemID
1234-1
5678-1
0987-1

table b - field name is itemID
1234-1
5555
6666

select itemID from tableA where itemID NOT IN ( select itemID from tableB ) yields:  5678-1 and 0987-1.

why doesnt this query return any records:

select i.itemID, itemDescription from items i where inactive = 0 and
itemid NOT IN
  ( select distinct itemID from purchaseorderdetails_v2 where POnumber in ( select ponumber from purchaseorders_v2 where orderdate > '1/1/2006' ) )
order by i.itemid


this query should return over 15000 records.  if i re-write it this way, i get the desired results:

select i.itemID, itemDescription from items i left join
  ( select distinct itemID from purchaseorderdetails_v2 where POnumber in ( select ponumber from purchaseorders_v2 where orderdate > '1/1/2006' ) ) as details
ON i.itemid = details.itemid
WHERE details.itemID is null
and inactive = 0
order by i.itemid

how can i write it using the NOT IN clause, and, more importantly, what am I missing that is causing the zero recordset to be returned.  I do understand that NOT IN = <> ALL, but don't know why it seems to behave different for the statements above.

thanks.
0
freezingHot
Asked:
freezingHot
1 Solution
 
freezingHotAuthor Commented:
found the issue... the subquery was returning a null row.  the items table does not allow nulls.

once i rewrote the subquery as:

select i.itemID, itemDescription from items i where inactive = 0 and
itemid NOT IN
  ( select distinct itemID from purchaseorderdetails_v2 where POnumber in ( select ponumber from purchaseorders_v2 where orderdate > '1/1/2006' ) and itemID IS NOT NULL)

it works like a champ.
order by i.itemid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now