Reputation: 797
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.
Upvotes: 15
Views: 38782
Reputation: 34281
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
Upvotes: 18
Reputation: 14873
Try this
SELECT Name, COUNT(Name) AS NumOccurrences
FROM table
GROUP BY Name
HAVING COUNT(*) > 0
Upvotes: 5
Reputation: 2679
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
)
Upvotes: 24