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 :-
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) bON (a.col1 = b.col1AND a.col2 = b.col2)
How about:
ReplyDeleteselect col1, col2, count(col2) from a group by col1,col2 having count(col2) > 1
try it out, this will too. that's why the group by is there :)
ReplyDeleteAnil, you are right...but the point I am trying to get out here is : Your query does "NOT" show the rows containing the dupes. It tells me what is duplicated. I want to print out all rows that are duplicated. Like in the above case - my query prints out A - B 2 times.
ReplyDeletePerhaps I have stripped off the example to a bare minimum and hence I am not able to explain the complexity involved. My work related query had sub selects - you really don't want to see PSoft queries do you ;)