Get duplicate entries from a table

Question

How can I get rows that were inserted more than once from a table? I allow my site users to have only one account per email but my script had a little bug so users where able to register more than once with the same email address. I want to get all the emails that has been recorded more than once. How is that possible using php/mysql?

Answer 1

select email from table group by email having count(email) > 1
should do the trick :)

Answer 2

Use this query:

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

Answer 3

You may want to try something like the following:

SELECT      email_address, 
            COUNT(*) number_of_dupes, 
            GROUP_CONCAT(user_id) users
FROM        users
GROUP BY    email_address
HAVING      COUNT(*) > 1;

Test case:

CREATE TABLE users (user_id int, email_address varchar(30));

INSERT INTO users VALUES (1, '[email protected]');
INSERT INTO users VALUES (2, '[email protected]');
INSERT INTO users VALUES (3, '[email protected]');
INSERT INTO users VALUES (4, '[email protected]');
INSERT INTO users VALUES (5, '[email protected]');
INSERT INTO users VALUES (6, '[email protected]');

Result:

+-------------------+-----------------+-------+
| email_address     | number_of_dupes | users |
+-------------------+-----------------+-------+
| [email protected] |               2 | 1,3   |
| [email protected] |               3 | 2,4,6 |
+-------------------+-----------------+-------+
2 rows in set (0.00 sec)