Answers.Body: <p>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.</p>
  
  <p>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)</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-11-21T19:12:28.933Z
Answers.LastActivityDate: 2016-11-21T19:12:28.933Z
Answers.UserDisplayName: petednz - fuzion
Answers.Body: <p>From the description it seems you need to filter the report using type id whereas the query applies the condition on <code>membership_status_id</code>. You may also need a subquery to address this -</p>
  
  <p>Instead of </p>
  
  <pre><code>WHERE ( mem_status_civireport.id NOT IN (1,2,3,4,5,6,7) )
  </code></pre>
  
  <p>try removing the contacts from the query itself which has those <code>membership_type_id</code>. Something like -</p>
  
  <pre><code>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)))
  </code></pre>
  
  <p>This will remove any contact having <code>membership_type_id</code> in the ids provided.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-11-22T09:43:04.480Z
Answers.LastActivityDate: 2016-11-22T09:43:04.480Z
Answers.UserDisplayName: jitendra - fuzion
Body: <p>I want to make a report that only shows contacts that <strong>don't have</strong> any of the listed membership id.</p>
  
  <p>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 <em>Joe</em> with a membership type 8, but <em>Joe</em> also have another membership of type 7, so I want to exclude <em>Joe</em> from the report).</p>
  
  <p>When trying to make this with Search Builder I got the same situation.</p>
  
  <p>I'm using Joomla! on that, so Drupal Views are <strong>not</strong> an option. I have the last CiviCrm version.</p>
  
  <blockquote>
    <p>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  </p>
    
    <p>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</p>
    
    <p>LEFT JOIN civicrm_membership_status mem_status_civireport
                              ON mem_status_civireport.id =
                                 membership_civireport.status_id</p>
    
    <p>WHERE ( mem_status_civireport.id NOT IN (1,2,3,4,5,6,7) )      </p>
    
    <p>ORDER BY contact_civireport.sort_name, contact_civireport.id, membership_civireport.membership_type_id  </p>
    
    <p>LIMIT 0, 50</p>
  </blockquote>
  
Tags: <civimember><joomla><custom-reports>
Title: Exclude contacts from report based on membership they have
ThreadText: # Exclude contacts from report based on membership they have
  
  (Asked by: username_0 on 2016-11-21)
  
  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
  
  **Tags:** civimember, joomla, custom-reports
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2016-11-21. Score: 2 | Accepted answer
  
  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 by username_2 on 2016-11-22. Score: 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.
  
  
  ---
  
  