Home > Oracle, SQL Server > SQL Query Trick to find duplicate records

SQL Query Trick to find duplicate records

Over the past few years there have been numerous instances where I needed to find records in a table where one field was different but two others were the same.  This proved painful until a friend of mine turned me on to this trick below.  You can use an inner join on the table itself to find the records. 

The syntax is simple yet it works:

select * from table1 a inner join table1 b where a.id <> b.id and a.Column = b.Column;

For an easy test I am going to select records from the Sample Northwind Database Products table.  There are records that have the same SupplierID and CategoryID field but different ProductID’s (primary key). 

select
  a.productid,
  a.productname,
  a.SupplierID,
  a.CategoryID,
  b.productid,
  b.productname,
  b.SupplierID,
  b.CategoryID 
  from Products a
  inner join Products b on a.ProductID <> b.ProductID
  where a.SupplierID = b.SupplierID
  and a.CategoryID = b.CategoryID

 

And below you see the records desired (duplicate column data). 

1-8-2010 12-37-17 AM

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 166 other followers

%d bloggers like this: