CODEWITHSUNDEEP
phpmysql
Simon
Simon

Reputation: 797

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.

Upvotes: 15

Views: 38782

Answers (3)

Juha Syrjälä
Juha Syrjälä

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

Pramendra Gupta
Pramendra Gupta

Reputation: 14873

Try this

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

Upvotes: 5

MightyE
MightyE

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

Related Questions