Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server - NOT IN Help

Posted on 2008-10-09
1
Medium Priority
?
201 Views
Last Modified: 2010-03-19
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
Comment
Question by:freezingHot
1 Comment
 
LVL 1

Accepted Solution

by:
freezingHot earned 0 total points
ID: 22681989
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Loops Section Overview

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question