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

​x
 
SELECT p1.Name, p2.NameFROM 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

 
NAME ID​A    1B    2C    1D    3E    2F    1D    3​

Result should be:

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

 
A,CA,FB,EC,FD,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

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

Advertisement

Answer