Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- Find suspect Memberships due to contributions being linked to multiple membership-payment records
SELECT
mempay.id AS mempay_id,
mempay.contribution_id AS contrib_id,
contrib.receive_date, contrib.total_amount,
mempay.membership_id AS mem_id, memtype.name,
c_contrib.display_name AS contrib_contact_name, c_contrib.id AS contrib_contact_id,
c_mem.id AS mem_contact_id, c_mem.display_name AS mem_contact_name
FROM
civicrm_membership_payment mempay
LEFT JOIN civicrm_membership mem ON mempay.membership_id=mem.id
LEFT JOIN civicrm_membership_type memtype ON mem.membership_type_id=memtype.id
LEFT JOIN civicrm_contribution contrib ON mempay.contribution_id=contrib.id
LEFT JOIN civicrm_contact c_mem ON mem.contact_id=c_mem.id
LEFT JOIN civicrm_contact c_contrib ON contrib.contact_id=c_contrib.id
WHERE mempay.contribution_id
IN ( SELECT contribution_id FROM civicrm_membership_payment GROUP BY contribution_id HAVING count(*)>1 )
ORDER BY contrib_id, mempay.id
;
@davejenx

This comment has been minimized.

Copy link

commented Nov 21, 2016

This will also show legitimate contributions where the contribution is for more than one membership.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.