CODEWITHSUNDEEP
phpmysql
bradg
bradg

Reputation: 723

How to list duplicate entries in database

how can i write a query to list duplicate entries in a database from the same category. The duplicates have the same value in the "name" column. I need to list only the duplicates in the same category so I can then delete the duplicate.

I am using this example from a search 

 SELECT email FROM tableName GROUP BY email HAVING count(email) > 1

That works for getting duplicates but it gets all duplicates, how can i rewrite it to get the duplicates from the same categories. In the above example, if i have an email that exists in the cat 1 and cat 4, it will be shown as duplicate, which is not the case. It should only list duplicates if the email exists twice or more in cat 1, or twice and more in cat 4 and so on.

Thanks.

Upvotes: 0

Views: 557

Answers (2)

Eggplant Jeff
Eggplant Jeff

Reputation: 1769

You can add more than one column to a group by. I.E.

SELECT email, category from tableName GROUP BY email, category HAVING count(email) > 1

That will show the email and category only where the email and category are both duplicate (I.E. same email twice with same category).

Upvotes: 2

Powerlord
Powerlord

Reputation: 88796

Add the category to the group by.

SELECT email FROM tableName GROUP BY email, category HAVING count(email) > 1

The only thing wrong with this is you won't be able to tell which category the duplicate is in unless you SELECT on it as well.

Upvotes: 0

Related Questions