SQLTeam.com | Weblogs | Forums

Query Duplicates Split over two tables


#1

I have two tables. TableA and TableB which contains customer records. The records are split over the two tables like so:
TableA contains a customers FirstName and LastName
TableB contains the customers Address
Both tables are connected through CustomerID

I want to do a select of all the customers that are duplicates i.e. the fields FirstName, LastName, and Address are the exact same in the duplicated records.

Anyone know how I could do this as the customer records are split across two tables A and B ?


#2

Look at using an INNER JOIN and possibly Row_number


#3

But how will that give me a list of the records that are duplicates ?


#4

Something like this:

select * from
(
	select *,
		count(*) over (partition by FirstName, LastName, Address) N
	from
		TableA a
		inner join TableB b on
			a.CustomerId = b.CustomerId
)s
where N > 1;