Query to get a list of contacts not tagged with a tag, but the user has multiple other tags

Question

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

Answer 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.