Finding duplicates based on count on different fields

Priyadarshini Vaishampayan 0 Reputation points


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

Azure SQL Database
{count} votes

1 answer

Sort by: Most helpful
  1. Susheel Bhatt 351 Reputation points

    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.

    0 comments No comments