Show all rows in MySQL that contain the same value

Question

Hi there,

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 1

You can do it with a sub select

SELECT * FROM table WHERE Name IN (
    SELECT Name FROM table GROUP BY Name HAVING count(*) > 1
)

Also if your distinction match is multiple columns, you can use cartesian sets:

SELECT * FROM table WHERE (firstName, lastName) IN (
    SELECT firstName, lastName FROM table GROUP BY firstName, lastName HAVING count(*) > 1
)

Answer 2

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

Answer 3

Try this

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