Find what is in one table and not in the other

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)

Find what is in one table and not in the other

Author

Topic

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2010-05-05 : 12:54:32

Hi,
In the interest of making this short I will show a subset here of what I have.
I have one table of zip codes
In another table I have various fields with one of them being sip codes. Now some time they get entered wrong so I want to pull them out.
So in table A, I have
Zips
14628
12344
12365
12374
12369
12623

In table B, I have
Address city zip
asd Buffalo
ghe Rochester 12344
kjh Troy 12A65
ghkj Yonkers 12374
uhe Bronx 1236u9
hjk Elmwood 12623

So my query should pull out
Address city zip
asd Buffalo NULL
kjh Troy 12A65
uhe Bronx 1236u9

I tried writing this query different ways and I come close but I cannot seem to get this. Does anyone have any ideas?
Thank you

ITM

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-05 : 13:23:20

This?

SELECT *
FROM table_B
WHERE zip IN 
      (SELECT zips
         FROM table_A);

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-05 : 13:26:51

Try this:

Declare @ZipTab table
(ZipCode varchar(10))

Insert into @ZipTab
Select 14628 union
Select 12344 union
Select 12365 union
Select 12374 union
Select 12369 union
Select 12623

Declare @TableB table
(
Address varchar(50),
city varchar(50),
zip varchar(10)
)

Insert into @TableB
Select 'asd', 'Buffalo', null union
Select 'ghe', 'Rochester','12344' union
Select 'kjh', 'Troy','12A65' union
Select 'ghkj', 'Yonkers','12374' union
Select 'uhe', 'Bronx' ,'1236u9' union
Select 'hjk', 'Elmwood' ,'12623'

Select B.* from @TableB B where not exists (Select 1 from @ZipTab z where z.ZipCode = B.zip)

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 14:43:05

quote:

Originally posted by ms65g

This?

SELECT *
FROM table_B
WHERE zip IN 
      (SELECT zips
         FROM table_A);

shouldnt it be NOT IN?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-05 : 15:41:20

NOT IN instead IN? maybe.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-05 : 18:25:50

Join method:

SELECT 
	B.*
FROM 
	@TableB AS B
LEFT OUTER JOIN
	@ZipTab AS Zip
	ON B.zip = Zip.ZipCode
WHERE
	Zip.ZipCode IS NULL

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2010-05-05 : 18:40:40

Thank you

quote:

Originally posted by ms65g

This?

SELECT *
FROM table_B
WHERE zip IN 
      (SELECT zips
         FROM table_A);

ITM

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2010-05-05 : 18:42:45

Thanks everyone I really appreciate your help

quote:
Originally posted by itmasterw

Thank you

quote:
Originally posted by ms65g

This?
SELECT *
FROM table_B
WHERE zip IN
(SELECT zips
FROM table_A);

ITM

ITM

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-05 : 19:23:22

You are welcome.

quote:
Originally posted by itmasterw

Thank you

quote:
Originally posted by ms65g

This?
SELECT *
FROM table_B
WHERE zip IN
(SELECT zips
FROM table_A);

ITM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources