CiviCRM Community Forums (archive)

After I merged individuals and organisations I now have a lot of duplicate relationships: a lot of "employee of" relationships between the same person and the same organisation. Any idea how to get rid of those?

Thanks. I did. To share my solution to the community I'll describe it here.

I used 2 queries. One is to show the duplicates:

SELECT * FROM ( SELECT contact_id_a, `contact_id_b`, `relationship_type_id`,  `start_date` , `end_date` , `is_active`, count(id) count_id, max(id) max_id FROM `civicrm_relationship` GROUP BY contact_id_a, `contact_id_b`, `relationship_type_id`, `start_date`, `end_date`, `is_active` ) rel_double WHERE count_id > 1

It shows the duplicates and one of the relationship id's (the highest number) of the duplicate rows.

Then I use that id to delete 1 relationship per duplicate:
DELETE FROM civicrm_relationship WHERE id IN (SELECT max_id FROM ( SELECT contact_id_a, `contact_id_b`, `relationship_type_id`,  `start_date` , `end_date` , `is_active`, count(id) count_id, max(id) max_id FROM `civicrm_relationship`  GROUP BY contact_id_a, `contact_id_b`, `relationship_type_id`, `start_date`, `end_date`, `is_active` ) rel_double WHERE count_id > 1)

You can use the first statement to check if you have more then one relationship and use the second again until it says: 0 row(s) deleted.

We now have a database with 9000 contacts and about 5500 relationships  (almost all "employee of") .
The first query above showed 351 duplicates (with 2 or more).

The second query was very fast and I had to do it 4 times to clear them all. Obviously that depends on the size of the database. But I think it cannot get much faster then direct SQL statements like this.

I do not have much inactive relationships at all yet. So in my case it does not make a difference.
But I think if every field in this table is the same you can remove the duplicates (unless you have custom fields to this relationship, then you have to add those as well to the query)