Exclude contacts from report based on membership they have

Question

I want to make a report that only shows contacts that don't have any of the listed membership id.

The following SQL code filters the memberships, but still lists contacts that have other memberships, including the ones I want to avoid (e.g.: it shows Joe with a membership type 8, but Joe also have another membership of type 7, so I want to exclude Joe from the report).

When trying to make this with Search Builder I got the same situation.

I'm using Joomla! on that, so Drupal Views are not an option. I have the last CiviCrm version.

SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, membership_civireport.membership_type_id as civicrm_membership_membership_type_id, membership_civireport.join_date as civicrm_membership_join_date, mem_status_civireport.name as civicrm_membership_status_name

FROM civicrm_contact contact_civireport INNER JOIN civicrm_membership membership_civireport ON contact_civireport.id = membership_civireport.contact_id AND membership_civireport.is_test = 0

LEFT JOIN civicrm_membership_status mem_status_civireport ON mem_status_civireport.id = membership_civireport.status_id

WHERE ( mem_status_civireport.id NOT IN (1,2,3,4,5,6,7) )

ORDER BY contact_civireport.sort_name, contact_civireport.id, membership_civireport.membership_type_id

LIMIT 0, 50

Answer 1

One solution could be to make a Smart Group (A) of those with Membership X or Y or Z, then have another Smart Group with the larger target group eg All Individuals (B), then use the Custom 'Include / Exclude' Search to do B not A.

This may not help you if your need an sql query but could be useful to others (if it works as I expect it would)

Answer 2

From the description it seems you need to filter the report using type id whereas the query applies the condition on membership_status_id. You may also need a subquery to address this -

Instead of

WHERE ( mem_status_civireport.id NOT IN (1,2,3,4,5,6,7) )

try removing the contacts from the query itself which has those membership_type_id. Something like -

WHERE (contact_civireport.id NOT IN (
  SELECT contact.id 
  FROM civicrm_contact as contact 
    INNER JOIN civicrm_membership membership
      ON contact.id = membership.contact_id 
      AND membership.is_test = 0 
  WHERE membership.membership_type_id IN (1,2,3,4,5,6,7)))

This will remove any contact having membership_type_id in the ids provided.