Not In - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Not In

Author

Topic

xrum
Yak Posting Veteran

87 Posts

Posted - 2011-04-21 : 15:01:22

Hi,

I need to build a query taht will show me records that are in Table 1, but are not in Table 2 based on the make-model-serial number combination

i know for fact that there are 4 records that differ, but my query always comes back blank. please help


SELECT  *  
from Table1 where MAKE+MODEL+[Serial Number]  not in
(select make+model+[serial number] from Table2)

Table 1 has 5 records,

when i change the query to "IN", i get 1 record. what am i doing wrong with the "NOT"?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-21 : 15:09:03

select MAKE,MODEL,[Serial Number]
from table1
except
select MAKE,MODEL,[Serial Number]
from table2

or

select * from table1 t1
where not exists
(select *
from table2 t2
where
t1.make = t2.make
and t1.model = t2.model
and t1.[serial number] = t2.[serial number]
)

Jim

Everyday I learn something that somebody else already knew

xrum
Yak Posting Veteran

87 Posts

Posted - 2011-04-21 : 15:11:40

thanks! so NOT IN was wrong? i saw many tutorials online that talk about it. or is it for special cases only?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-21 : 15:22:12

You have to be careful with it, especially when you column might have nulls

select case when 'c' not in ('a','b',null) then 'C is not in the list' else 'c is in the list' end

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources