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).
