Comparing Rows in 2 Different Tables

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)

Comparing Rows in 2 Different Tables

Author

Topic

srussell
Starting Member

9 Posts

Posted - 2008-08-07 : 13:54:05

I have two tables that store a list of addresses for a user. I need to compare all the address entries in one table to the other for that user and then return any row that is different. Does anyone have an idea on how to do this?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-07 : 14:01:30

[code]
select * from
mytable1 a
Full outer join
mytable2 b
on a.Address1 = b.Address1
and a.Address2 = b.Address2
and a.City = b.City
and a.Zip = b.Zip
and a.State = b.state
where
a.Address1 is null
or
b.Address1 is null
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 14:02:05

[code]SELECT t1.address
FROM Table1 t1
LEFT JOIN Table2 t2
ON t2.address=t1.address
WHERE t2.address IS NULL
UNION ALL
SELECT t2.address
FROM Table2 t2
LEFT JOIN Table2 t2
ON t2.address=t1.address
WHERE t2.address IS NULL
[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-07 : 14:43:35

Not Sure though:( It returns distinct rows)

Select address 1,address 2 from table a
except
select address 1, address2 from table b

srussell
Starting Member

9 Posts

Posted - 2008-09-25 : 10:07:36

This is what I am trying and it is just returning all the records from table one with that id number. Any help would be great.

select * from table1 as m
where not exists (select * from table2
where memNum = m.memNum
and memName = m.memName
and memAdd1 = m.memAdd1
and memAdd2 = m.memAdd2
and memCity = m.memCity
and memState = m.memState
and memZip5 = m.memZip5
and memZip4 = m.memZip4
and id= '1') and id= '1'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources