I want to pull out duplicate records in a MySQL Database. This can be done with:
SELECT address, count(id) as cnt FROM list
GROUP BY address HAVING cnt > 1
100 MAIN ST 2
JIM JONES 100 MAIN ST
JOHN SMITH 100 MAIN ST
The key is to rewrite this query so that it can be used as a subquery.
SELECT firstname, lastname, list.address FROM list INNER JOIN (SELECT address FROM list GROUP BY address HAVING COUNT(id) > 1) dup ON list.address = dup.address;