Monday, December 01, 2008

SQL - Find Possible Duplicate Records in Multiple Columns

 

Below is the code used to find possible duplicate records across multiple columns. Below I am trying to find duplicate rows in col1, col2 combined eg.

Consider the following ERD :- 

ERD

Table data:-

table data

Here row 2 and 3 are duplicate and the following SQL detects and finds such rows:-

SELECT a.* FROM test a INNER JOIN 
( 
    SELECT col1, col2 FROM test GROUP BY col1, col2 HAVING Count(*) >1 
) b 
ON (a.col1 = b.col1 
AND a.col2 = b.col2)