On Fri, Jul 24, 2009 at 7:10 AM, Fred Jones<fredthejonester at gmail.com> wrote: > I have this: > > 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 > GROUP BY civicrm_contact.id; > > which is good. I get results like > > 1 4 > 2 0 > 3 0 > 5 3 > > 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. So ask that question. Untested... SELECT civicrm_contact.id , COUNT(civicrm_entity_tag.tag_id) FROM civicrm_contact WHERE 1 > ( select count(*) from civicrm_entity_tag inner join civicrm_tag on civicrm_tag.id = civicrm_entity_tag.tag_id where civicrm_entity_tag.contact_id = civicrm_contact.id AND civicrm_tag.name LIKE "%Primary%" OR civicrm_tag.name LIKE "%Secondary%" ); I didn't understand why you had left joins. Apologies if I missed a part of your logic... -- Matt Warden Cincinnati, OH, USA http://mattwarden.com This email proudly and graciously contributes to entropy.