Select Statement 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)

Select Statement NOT IN

Author

Topic

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-11-03 : 14:56:42

Hi I am a Junior Web Developer for an advertising agency. I need help with a sql statement. Can anyone help? Here is the problem: I have 2 tables that have codes. One table called hotel has outdated codes. the other tables has the same 'hotel' codes as well as new ones. Now I want to make a select statement that will compare both tables and give me a listing of all the codes that are not in the 'hotel' table. This what i think it should be:



SELECT     holidexCode AS C
FROM         hotel
WHERE     (holidexCode NOT IN
                          (SELECT     holidexCode
                            FROM          NewHotel 
										))

This however does not give me what i need. Can anyone help?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-03 : 15:04:34

quote:
a listing of all the codes that are not in the 'hotel' table

select holidexCode from NewHotel nh
left join Hotel h on (nh.holidexCode = h.holidexCode )
where h.holidexCode is null

shows you all codes from NewHotel that have no matching code in Hotel

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-03 : 15:06:09

quote:
Originally posted by jgonzalez14

Hi I am a Junior Web Developer for an advertising agency. I need help with a sql statement. Can anyone help? Here is the problem: I have 2 tables that have codes. One table called hotel has outdated codes. the other tables has the same 'hotel' codes as well as new ones. Now I want to make a select statement that will compare both tables and give me a listing of all the codes that are not in the 'hotel' table. This what i think it should be:

SELECT holidexCode AS C
FROM NewHotel
WHERE holidexCode NOT IN
(SELECT holidexCode
FROM Hotel )

This however does not give me what i need. Can anyone help?

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-11-03 : 15:22:16

That seemed to do the trick thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources