SQL Server Performance Forum – Threads Archive

NOT IN problem

Hello, This is for SQL 2000. I am trying to do a "NOT IN" query to return customer accounts that have billing in 2006 but not 2005. My query, as follows, fails to return a record it should: SELECT CustNo from GeneralLedger
WHERE (PostingYear = 2006 )
AND (GLAccount = 40000)
AND (CustNo NOT IN
(SELECT CustNo FROM GeneralLedger
WHERE (GLAccount = 40000) AND (PostingYear = 2005))
)
ORDER BY CustNo; CustNo 5107 appears in 2006 but not 2005, but does not show up in the results. If I run the subquery alone: SELECT CustNo FROM GeneralLedger
WHERE (GLAccount = 40000) AND (PostingYear = 2005); CustNo 5107 does not appear. If I run the main query without the subquery: SELECT CustNo from GeneralLedger
WHERE (PostingYear = 2006 )
AND (GLAccount = 40000)
ORDER BY CustNo; CustNo 5107 does appear. What am I doing wrong?? Thanks, Bob Shepherd

Are there any NULLs in your column? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Yes, does that matter?

Try to use aliases:
select new.custNo
from generalLeadger new
where new.glAccount = 4000 and
not exists(select *
from generalLeadger old
where old.CustNo = new.CustNo and
old.glAcount = 4000 and
old.postingYear = 2005)

Thank you Frank, When I add "and CustNo is not null" to the subquery the problem goes away. Bob

Sorry, I went home after posting. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Check this out:<a target="_blank" href=http://snipurl.com/lnd2>http://snipurl.com/lnd2</a> It’s a great explanation of your issue. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

]]>