[RESOLVED] Find duplicate and display them all

[curb]

I found a query from another thread that only pulls one row out of the X amount of duplicates found. How do I find duplicate rows and display all the duplicates instead of just one?

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

[Piranha]

Exactly as you do, just loop through the result to show all results.

[curb]

How would I loop this?

The only loop I know is while

while($row = mysql_fetch_array($query)){
echo $row['email'];
}

[Sxooter]

curb wrote:

I found a query from another thread that only pulls one row out of the X amount of duplicates found. How do I find duplicate rows and display all the duplicates instead of just one?

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

That query pretty much gets rid of the duplicates. What you need to do is to either use it as a subselect or use a different query. this one will give you all the dupes:

select distinct a.email, a.id from users a join users b on (a.email=b.email and a.id <> b.id) 

See what we're doing there? We're joining the table to itself, on the email, but with the id field not matching. if you don't have an id field, then you'll need to create one and populate it or find a field to serve that purpose.

[curb]

Ah very nice. I've tested it and it works. I'll take a deeper look into this. Thanks

[Piranha]

Sorry, I misunderstood what you ment :o

[Sxooter]

Note that the query as I entered here will give you the same row more than once. change the a.id<>b.id to a.id>b.id to get rid of the dupes of the dupes.