Find duplicate record in mysql

Question

I have a table named property ,structured like this.

property_id | ListingKey | name
1             abkjhj-123   abc
2             abkjhj-123   abc1
3             abkjhj-124   abc4

I want duplicate records based on ListingKey. I write this query but not sure is this correct or not?

SELECT a.property_id
FROM property a
INNER JOIN property b ON a.property_id = b.property_id
WHERE a.ListingKey <> b.ListingKey

Thanks in advance.

possible duplicate of [Finding duplicate values in MySQL](http://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql) — Gunaseelan, Oct 21 '14 at 10:33

score 4 · Accepted Answer · answered Oct 21 '14 at 10:25

You can avoid the self join with a Having clause:

SELECT a.ListingKey 
FROM property a
GROUP BY a.ListingKey
HAVING COUNT(a.property_id) > 1;

SqlFiddle

Update : If you want a list of all the ids in the Duplicate as well:

SELECT a.ListingKey, GROUP_CONCAT(a.property_id)
FROM property a
GROUP BY a.ListingKey
HAVING COUNT(a.property_id) > 1;

score 0 · Answer 2 · answered Oct 21 '14 at 10:25

Your query is not correct. Here is another method:

select p.*
from property p
where exists (select 1
              from property p2
              where p2.listingkey = p.listingkey and p2.property_id <> p.property_id
             );

If you care about performance, add an index on property(listingkey, property_id).

Find duplicate record in mysql

2 Answers2