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 |
|
xoqon
Starting Member
3 Posts |
 Posted - 2008-01-14 : 10:23:54
|
| Hi folksI have just encountered a problem which makes no sense to me.If"SELECT * FROM ERP_Codes WHERE ERP_CodeID = 9300" - returns 1 recordand"SELECT * FROM Ingredients WHERE ERP_CodeID = 9300" - does not return a recordthen why does"SELECT ERP_Codes.ERP_CodeIDFROM ERP_CodesWHERE ERP_Codes.ERP_CodeID NOT IN(SELECT ERP_CodeID from Ingredients)"not return any records?Also,"SELECT ERP_Codes.ERP_CodeID, Ingredients.ERP_CodeIDFROM ERP_Codes LEFT JOIN Ingredients ON ERP_Codes.ERP_CodeID = Ingredients.ERP_CodeIDWHERE Ingredients.ERP_CodeID Is Null"returns the result set I'm after.I just can't understand why the NOT IN isn't working (I've used this syntax 1000s of times before!!)Is it a server problem or am I just plain wrong?Thanks(Oh, removing "NOT" returns the right amount of records!) |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-01-14 : 10:31:01
|
| i guess it's because you're trying to do a comparison with a null, which will always be null right? you wouldn't say = null or <> null would you?Em |
 |
|
|
xoqon
Starting Member
3 Posts |
Posted - 2008-01-14 : 10:33:51
|
| no,"SELECT ERP_CodeID from Ingredients" returns 1500 records! |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-01-14 : 10:45:41
|
| is there any ERP_CodeID value in the Ingredients table which is Null?what does this return???SELECT * FROM Ingredients where erp_codeid is null |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-01-14 : 10:47:21
|
| Use LEFT JOIN instead of NOT IN. NOT IN has known problem with Nulls.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
xoqon
Starting Member
3 Posts |
Posted - 2008-01-14 : 10:56:58
|
| yep, had a null. Entering a value resolved the issue (never encountered this before...) Will use the LEFT JOIN method in future. Many thanks Andrew / Harsh |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
|
|
|
[RESOLVED] "NOT IN" - not right!