mySQL – Check Table for duplicates efficiently

I have a table “people” where (for simplicity) everyone has an ID, this ID is not unique so two entries can have the same ID. I now want to find all duplicates which I would do like this:

SELECT p1.Name, p2.Name
FROM Person p1 JOIN Person p2 ON p1.ID = p2.ID

Sample data would be something like this:

NAME ID

A    1
B    2
C    1
D    3
E    2
F    1
D    3

Result should be:

A,C
A,F
B,E
C,F
D,D

But I would like to only compare entry A to all others, then B to all others EXCEPT A, etc. How do I do this?

Answer

I think that you want:

SELECT p1.Name, p2.Name
FROM Person p1 
JOIN Person p2 ON p1.ID = p2.ID AND p1.Name < p2.Name

Advertisement

Answer