Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 "NOT IN" clause doen't work in a subquery

Author  Topic 

ttran
Starting Member

23 Posts

Posted - 2013-12-11 : 11:45:01
Could someone tell me what is wrong with my query, please.
Every thing is work fine except the "NOT IN" clause doesn't work. I didn't get any error. The "NOT IN" clause is just simply ignored.
It has been driving me crazy.
Any help would be greatly appreciated.
Thank you so much.

select a.id,a.cd,a.co,a.esd,a.eed,a.psd,a.ped,b.yr,b.sc,b.id,b.ed,b.ld
from fre a join enr b on a.id = b.id
where a.del = 0 and b.del = 0
and a.cd in ('f', 'r')
and a.esd > '10/02/2013'
and b.yr = 2013 and b.ed <= '10/02/2013'
and (b.ld is null or b.ld >= '10/02/2013')
and a.id not in (select stuid from tblDC where stat = 'c')
order by a.id

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-11 : 12:11:06
Sample data and expected output?

My guess us that there is a NULL in there someplace and NOT IN and nulls have some oddities:
http://stackoverflow.com/questions/129077/not-in-constraint-and-null-values

Here are some link to help you prepare your sample data and expected output:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

ttran
Starting Member

23 Posts

Posted - 2013-12-11 : 12:56:26
Thank you so much, Lamprey.
It did drive me crazy for a while yesterday.

But, I run the same query, and it works today.
I don't know how to explain that.
Is it possible that there was some kind of flaky or false connection because I have a remote connection to the server?
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-11 : 13:02:11
>>Is it possible that there was some kind of flaky or false connection because I have a remote connection to the server?
The much more likely scenario is that the data changed. probably this statement returned at least one null yesterday but not today:
select stuid from tblDC where stat = 'c'

I haven't looked at Lamprey's links but I tend to use the LEFT OUTER JOIN technique to find rows that don't exist in a different table.

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -