Solved

SQL Server - NOT IN Help

Posted on 2008-10-09
1
182 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

813 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now