Answers.Body: <p>I don't know how this would translate to Drupal Views, but here is the SQL to do it: </p>
  
  <pre><code>SELECT 
    contact.id
  FROM civicrm_contact contact
  LEFT JOIN civicrm_entity_tag et ON
    et.entity_table = 'civicrm_contact' AND 
    et.entity_id = contact.id AND
    et.tag_id = 1 /* ID of the tag you want to exclude */
  WHERE et.id IS NULL;
  </code></pre>
  
  <p>Note that <code>et.tag_id = 1</code> must be in the <code>ON</code> clause and not in the <code>WHERE</code> clause. </p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-17T19:50:10.817Z
Answers.Text: ok, I could probably do that alter in a hook_views_query_alter() implementation
Answers.UserDisplayName: jackrabbithanna
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-18T21:40:21.967Z
Answers.Text: This isn't working for me....I'm still getting results for a contact with the tag I want to exclude, if they have another tag....
Answers.UserDisplayName: jackrabbithanna
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-18T21:41:12.213Z
Answers.Text: Here's what the query I have now after some query mods via hook_views_query_alter: 
  
  SELECT civicrm_contact.id AS id, civicrm_contact.display_name AS civicrm_contact_display_name
  FROM 
  {civicrm_contact} civicrm_contact
  LEFT JOIN {civicrm_entity_tag} civicrm_entity_tag ON civicrm_contact.id = civicrm_entity_tag.entity_id AND (civicrm_entity_tag.entity_table = 'civicrm_contact' AND civicrm_entity_tag.entity_id = '35')
  LEFT JOIN {civicrm_tag} civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id
  WHERE (( (civicrm_contact.id = '21033') AND (civicrm_tag.id IS NULL ) ))
  LIMIT 10 OFFSET 0
Answers.UserDisplayName: jackrabbithanna
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-18T23:07:25.037Z
Answers.Text: why if  et.tag_id = 1 , is 1 excluded from the query? Wouldn't that statement include it?
Answers.UserDisplayName: jackrabbithanna
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-19T03:48:09.550Z
Answers.Text: `et.tag_id = 1` is to specify which tag you want to operate on. Looks like you'd set this to 35 in your example.
Answers.UserDisplayName: Sean
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-19T03:51:08.157Z
Answers.Text: Looks like your query isn't working because it's also joining on `civicrm_tag`. An exclusion join will only work if you join directly on *one* table. You can't join on another one which is part of the exclusion. Hard to explain this fully in such a small comment. If you really must join on `civicrm_tag`, then you'd need to use a sub-query or a temporary table. But I suspect for your purposes you *don't* need to join on `civicrm_tag` if you already know the id of the relevant tag. We can chat more on Mattermost (or phone) if you like.
Answers.UserDisplayName: Sean
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-17T19:47:48.017Z
Answers.LastActivityDate: 2017-07-17T19:47:48.017Z
Answers.UserDisplayName: Sean
Body: <p>I'm building a View that lists contacts.  The View needs to list all contacts that are not tagged with a certain tag.  The problem is if a contact is tagged with another tag, it will still show in the list, because of the LEFT JOIN to the tag table...In this example I'm filtering the list to one contact for simplicity's sake. 
  The contact in question has two tags.  One of them is the one I want to filter out.  Yet the contact still shows in the list because it has another tag....</p>
  
  <p>I also want to include contacts in the list that have no tags at all, thus the </p>
  
  <p>part of the query ( (civicrm_tag.id IS NULL ) OR (civicrm_tag.id NOT IN  ('35')) )</p>
  
  <pre><code>SELECT civicrm_contact.id AS id, civicrm_contact.display_name AS civicrm_contact_display_name
  FROM 
  {civicrm_contact} civicrm_contact
  LEFT JOIN {civicrm_entity_tag} civicrm_entity_tag ON civicrm_contact.id = civicrm_entity_tag.entity_id AND civicrm_entity_tag.entity_table = 'civicrm_contact'
  LEFT JOIN {civicrm_tag} civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id
  WHERE (( (civicrm_contact.id = '21033') )AND( (civicrm_tag.id IS NULL ) OR (civicrm_tag.id NOT IN  ('35')) ))
  LIMIT 10 OFFSET 0
  </code></pre>
  
Tags: <drupal-views><sql>
Title: Query to get a list of contacts not tagged with a tag, but the user has multiple other tags
ThreadText: # Query to get a list of contacts not tagged with a tag, but the user has multiple other tags
  
  (Asked by: username_0 on 2017-07-17)
  
  I'm building a View that lists contacts. The View needs to list all contacts that are not tagged with a certain tag. The problem is if a contact is tagged with another tag, it will still show in the list, because of the LEFT JOIN to the tag table...In this example I'm filtering the list to one contact for simplicity's sake.
  The contact in question has two tags. One of them is the one I want to filter out. Yet the contact still shows in the list because it has another tag....
  
  I also want to include contacts in the list that have no tags at all, thus the
  
  part of the query ( (civicrm\_tag.id IS NULL ) OR (civicrm\_tag.id NOT IN ('35')) )
  
  ```
  SELECT civicrm_contact.id AS id, civicrm_contact.display_name AS civicrm_contact_display_name
  FROM 
  {civicrm_contact} civicrm_contact
  LEFT JOIN {civicrm_entity_tag} civicrm_entity_tag ON civicrm_contact.id = civicrm_entity_tag.entity_id AND civicrm_entity_tag.entity_table = 'civicrm_contact'
  LEFT JOIN {civicrm_tag} civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id
  WHERE (( (civicrm_contact.id = '21033') )AND( (civicrm_tag.id IS NULL ) OR (civicrm_tag.id NOT IN  ('35')) ))
  LIMIT 10 OFFSET 0
  
  ```
  
  **Tags:** drupal-views, sql
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-07-17. Score: 1
  
  I don't know how this would translate to Drupal Views, but here is the SQL to do it:
  
  ```
  SELECT 
    contact.id
  FROM civicrm_contact contact
  LEFT JOIN civicrm_entity_tag et ON
    et.entity_table = 'civicrm_contact' AND 
    et.entity_id = contact.id AND
    et.tag_id = 1 /* ID of the tag you want to exclude */
  WHERE et.id IS NULL;
  
  ```
  
  Note that `et.tag_id = 1` must be in the `ON` clause and not in the `WHERE` clause.
  
  > **Comments:**
  > **username_0**: ok, I could probably do that alter in a hook_views_query_alter() implementation  
  > **username_0**: This isn't working for me....I'm still getting results for a contact with the tag I want to exclude, if they have another tag....  
  > **username_0**: Here's what the query I have now after some query mods via hook_views_query_alter:   SELECT civicrm_contact.id AS id, civicrm_contact.display_name AS civicrm_contact_display_name FROM  {civicrm_contact} civicrm_contact LEFT JOIN {civicrm_entity_tag} civicrm_entity_tag ON civicrm_contact.id = civicrm_entity_tag.entity_id AND (civicrm_entity_tag.entity_table = 'civicrm_contact' AND civicrm_entity_tag.entity_id = '35') LEFT JOIN {civicrm_tag} civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id WHERE (( (civicrm_contact.id = '21033') AND (civicrm_tag.id IS NULL ) )) LIMIT 10 OFFSET 0  
  > **username_0**: why if  et.tag_id = 1 , is 1 excluded from the query? Wouldn't that statement include it?  
  > **username_1**: `et.tag_id = 1` is to specify which tag you want to operate on. Looks like you'd set this to 35 in your example.  
  > **username_1**: Looks like your query isn't working because it's also joining on `civicrm_tag`. An exclusion join will only work if you join directly on *one* table. You can't join on another one which is part of the exclusion. Hard to explain this fully in such a small comment. If you really must join on `civicrm_tag`, then you'd need to use a sub-query or a temporary table. But I suspect for your purposes you *don't* need to join on `civicrm_tag` if you already know the id of the relevant tag. We can chat more on Mattermost (or phone) if you like.  
  
  ---
  
  