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.
| 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.ldfrom 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 |
|
|
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? |
 |
|
|
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 OptimizerTG |
|
|
|
|
|
|
|
|
"NOT IN" clause doen't work in a subquery