[thelist] Stuck on SQL

[Chris Anderson]

> What I really want, however, is to get all contacts which do NOT have
> a tag where the tag name is:
> 
> civicrm_tag.name LIKE "%Primary%"  OR civicrm_tag.name  LIKE "%Secondary%"
> 
> I don't care if he has zero tags or if he has 6 tags. I just care that
> he has zero tags where the tag has "Primary" or "Secondary" somewhere
> in its name.
> 
> I am not succeeding in figuring out how to do this with one SQL. :(


 
How about counting the number of tags, then using the HAVING clause to just select those with 0 tags:
 
SELECT civicrm_contact.id , COUNT(civicrm_entity_tag.tag_id)
 FROM civicrm_contact
 LEFT JOIN civicrm_entity_tag ON civicrm_entity_tag.contact_id =
civicrm_contact.id
 LEFT JOIN civicrm_tag ON civicrm_tag.id = civicrm_entity_tag.tag_id
WHERE civicrm_tag.name LIKE "%Primary%"  OR civicrm_tag.name  LIKE "%Secondary%"
GROUP BY civicrm_contact.id
HAVING COUNT(civicrm_entity_tag.tag_id) = 0

Chris