CODEWITHSUNDEEP
phpmysql
uiTeam324
uiTeam324

Reputation: 1245

Find duplicate record in mysql

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.

Upvotes: 0

Views: 79

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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).

Upvotes: 0

StuartLC
StuartLC

Reputation: 107247

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;

Upvotes: 4

Related Questions