Finding duplicates based on count on different fields

Question

Hello!

I have a Contact table which has columns like ContactID, Nationalidnumber, Firstname, Birthdate, Mobilephone, Emailaddress, CreatedOn and so on. I want to find duplicate contactIDs where two or more rows has:

1. same Nationalidnumber AND
2. same (Birthdate + Mobilephone) combination AND
3. same (Birthdate + Emailaddress) combination AND
4. same (Firstname + Mobilephone) combination AND
5. same (Firstname + Emailaddress) combination Can someone help me with how can I solve this query?

Regards PP

Answer 1

You can use the following SQL query to find duplicate ContactIDs based on the conditions you mentioned:

SELECT t1.ContactID, t2.ContactID
FROM Contact t1
INNER JOIN Contact t2 ON t1.ContactID <> t2.ContactID
WHERE t1.Nationalidnumber = t2.Nationalidnumber
AND (t1.Birthdate + t1.Mobilephone) = (t2.Birthdate + t2.Mobilephone)
AND (t1.Birthdate + t1.Emailaddress) = (t2.Birthdate + t2.Emailaddress)
AND (t1.Firstname + t1.Mobilephone) = (t2.Firstname + t2.Mobilephone)
AND (t1.Firstname + t1.Emailaddress) = (t2.Firstname + t2.Emailaddress)

This query will return pairs of ContactIDs where the conditions are met. The INNER JOIN is used to join the Contact table with itself to compare each row with all the other rows. The WHERE clause specifies the conditions for finding duplicates based on the columns you mentioned.