Simon Simon - 2 months ago 7
MySQL Question

Show all rows in MySQL that contain the same value

I have a MySQL database:

ID | Name
1 | Bob
2 | James
3 | Jack
4 | Bob
5 | James


How would I return a list of all the columns where the same name appears more than once, eg, I'd like to return this:

1 | Bob
2 | James
4 | Bob
5 | James


I've written a count query:

SELECT Name, COUNT(Name)
AS NumOccurrences
FROM table
GROUP BY Name
HAVING ( COUNT(Name) > 1 )


But that just returns something like this:

Bob | 2
James | 2


Whereas I want to return the full rows returned.

Any help would be greatly appreciated, thanks.

Answer

Try this sql query:

select distinct a.name, a.id 
from table a, table b 
where a.name = b.name and a.id != b.id
Comments