Author Topic: How would I query the database to find records with two "work" emails that are.. (Read 373 times)

acnao · April 03, 2013, 02:10:40 pm

identical? I also need to figure out if an individual (or Org) has two identical websites listed of the same type as well...

The issue is, when I pull an event participant export and include the three email fields (work, home and billing), it will list someone twice (two rows in the CSV) if they have a duplicate email of the same type exists in their contact record... So I need to find every record that has a duplicate email for the same type of email... AND the issue also occurs if they have a duplicate website in their record, so I need to find all those too.

Example: Bob, an Individual, happens to have TWO 'Work' emails in his record.. and they are both "bob at bob.com".

Therefore, Bob will show up twice (two rows) in event participant exports... which we don't want. He should only have one row in the exported CSV.

So my question is... how can I pull a list or query the database directly (and export to CSV) for anyone who might have duplicate "Work"+"Work" emails so I can remove the duplicate... and I also need to search for Home and Billing email too because that will cause two rows in an export...

Does this question make sense? It's been a rough day, my brain is fried.

Hershel · April 07, 2013, 11:48:12 am

Something like

Code: [Select]

  SELECT contact_id, email, location_type_id  
  FROM civicrm_email  
  WHERE contact_id IN 
    (
      SELECT contact_id  
      FROM civicrm_email 
      GROUP BY email, `location_type_id` 
      HAVING COUNT(*) > 1
    )
ORDER BY email,location_type_id;

CiviCRM Community Forums (archive)

News:

Author Topic: How would I query the database to find records with two "work" emails that are.. (Read 373 times)

acnao

How would I query the database to find records with two "work" emails that are..

Hershel

Re: How would I query the database to find records with two "work" emails that are..