MySQL look for duplicates on multiple fields

Question

I have a MySQL database with the following fields:

id, email, first_name, last_name

I want to run an SQL query that will display rows where id and email exists more than once.

Basically, the id and email field should only have one row and I would like to run a query to see if there are any possible duplicates

Does your database structure define id as a primary key? If it does, there should be no way you have duplicates. — AdamMc331, Feb 20 '15 at 18:28
no, i have another field that is a primary key called member_id — John, Feb 20 '15 at 18:30
possible duplicate of [Find duplicate records in MySQL](http://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql) — Marcus Adams, Feb 20 '15 at 18:32

score 0 · Answer 1 · edited May 23 '17 at 10:24

0

You'll want something like this:

select field1,field2,field3, count(*)
from table_name
group by field1,field2,field3
having count(*) > 1

See also this question.

edited May 23 '17 at 10:24

Community

1
1

answered Feb 20 '15 at 18:30

Nebula42

71
4

score 0 · Answer 2 · answered Feb 20 '15 at 18:31

You can search for all ids that meet a specific count by grouping them and using a having clause like this:

SELECT id, COUNT(*) AS totalCount
FROM myTable
GROUP BY id
HAVING COUNT(*) > 1;

Anything this query returns has a duplicate. To check for duplicate emails, you can just change the column you're selecting.

score 0 · Accepted Answer · answered Feb 20 '15 at 18:32

If you just want to return the id and email that are duplicated, you can just use a GROUP BY query:

SELECT id, email
FROM yourtable
GROUP BY id, email
HAVING COUNT(*)>1

if you also want to return the full rows, then you have to join the previous query back:

SELECT yourtable.*
FROM
  yourtable INNER JOIN (
    SELECT id, email
    FROM yourtable
    GROUP BY id, email
    HAVING COUNT(*)>1
  ) s
  ON yourtable.id = s.id AND yourtable.email=s.email

MySQL look for duplicates on multiple fields

3 Answers3