Display all duplicated values and their IDs from a table (SQL)

I’m using SQL Sever. I have a table that looks like this:

+---------+--------------+
| ID         Name        |  
+---------+--------------+
| 1       | John   
| 2       | Charles  
| 3       | Molly  
| 4       | John
| 5       | Kathy
| 6       | Mike
| 7       | Charles
| 8       | Kathy

Is there a way I can retrieve all the duplicated values only? Not just one of them, but all.

This is the desired output:

+---------+--------------+
| ID         Name        |  
+---------+--------------+
| 1       | John     
| 4       | John
| 5       | Kathy
| 8       | Kathy
| 7       | Charles
| 2       | Charles

Answer

This works in SQL Server.

SELECT Id, Name
FROM MyTable AS T1
WHERE
    EXISTS
    (
        SELECT Name
        FROM MyTable
        WHERE Name = T1.Name
        GROUP BY Name
        HAVING COUNT(*) > 1
    );

Advertisement

Answer