>
> Can someone help me with the SQL to find duplicate records in a table where
> a duplicate is considered two out of five matching fields? I thought:
> SELECT * ;
> FROM table;
> WHERE keyfield IN ;
> (SELECT keyfield ;
> FROM tablel ;
> GROUP BY keyfield, field2;
> HAVING count(*) > 1)
> would do the trick, but it doesn't work. TIA
>
> Erik
SELECT * ;
FROM table;
WHERE keyfield IN ;
(SELECT keyfield, count(*) as cnt ;
FROM tablel ;
GROUP BY keyfield, field2;
HAVING cnt > 1)
....but then, I've never done this, to have two fields in a subquery used
for Where ... in ( ). Maybe it could give you some extra records, if
keyfield is numeric and equal to number of appearances in table1.
Really, what does SQL do in this case? Does it compare table.keyfield
with subquery.keyfield only, or compares it with subquery.cnt too?