MySQL Select all non-unique rows

[jme]

Hi guys.

I have a users table that has over 10,000 registered users (from a database that I've inherited). The original code allows for multiple registrations with the same email address which I want to eliminate.

What I would like to do is run an SQL query on the table and it give me all the rows where the email address is NOT unique in the table so I can trawl through them to remove.

Any suggestions or guidance would be great. Many thanks.

[Weedpacket]

Select email,count(email) from thetable group by email having count(email)>1

If I got the syntax and any peculiarities your DBMS has with it right. Then you can look for the records with those addresses and decide which ones you want to keep.

[jme]

Thanks for the suggestion Weedpacked. That worked to an extent showing which emails have more than one entry with output looking like this:

email                     count(email)
[e-mail]        2
[e-mail]    2
[e-mail]         3

But I was hoping to get an output like:

id     email                   Name           Status    ect ... 
232  [e-mail]       Bob                 1
434  [e-mail]       Robert             0
23    [e-mail]   Mark               1
543  [e-mail]   mark               0

Hope that makes sense. Is this possible?

Jamie

[bradgrafelman]

So perhaps something like this will work:

SELECT * FROM myTable WHERE email IN (SELECT email FROM myTABLE GROUP BY email HAVING count(email)>1)

[Piranha]

I suggest that you order by email, to make it possible for you to visually see what rows contains the same mail. Just to add an order by part in bradgrafelman's query:

SELECT * FROM myTable WHERE email IN (SELECT email FROM myTABLE GROUP BY email HAVING count(email)>1) order by email