CiviCRM Community Forums (archive)

I don't know about you but a couple times a week in our rather large CiviCRM database we run into a situation where someone has a duplicate contact record, and by this I mean the same person + same address, but with a different email address.

I was looking for an efficient way to track down names that are potential duplications, and came up with this query.

SELECT t.id,
       t.last_name,
       a.street_address,
   a.city
  FROM civicrm_contact t
  JOIN civicrm_address a ON a.contact_id = t.id
  JOIN (SELECT n.last_name
          FROM civicrm_contact n
      GROUP BY n.last_name
        HAVING COUNT(*) > 1) nn ON nn.last_name = t.last_name;

You can visually scan the result, looking for last names with identical streets and cities, and then investigate to see if it is a duplicate contact.

Anyone found a better way to handle this?  Suggestions, improvements?

there is also the issue of how we handle same person - two emails - two drupal accounts. Stoob are you trying to deal with that too?

Apologies if this seems to be hijacking - totally ignore above if you aren't being rattled by issue of one person - two drupal accounts - two civicrm records (or any variations such as one person, one drupal account, two civicrm records)

Just spotted there is a Drupal 'user merge' project just coming out of the shadows - http://drupal.org/project/usermerge