[thelist] Stuck on SQL

Chris Anderson Chris at activeide.com
Fri Jul 24 06:39:24 CDT 2009

> 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 =
 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


More information about the thelist mailing list