[thelist] Stuck on SQL

[Fred Jones]

> 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

I tried such a thing but it gets zero rows. Based on what Rudy did for
me once, I actually think it's like this:

SELECT civicrm_contact.id
 FROM civicrm_contact
 LEFT JOIN civicrm_entity_tag ON civicrm_entity_tag.contact_id =
civicrm_contact.id
 LEFT JOIN (SELECT id FROM civicrm_tag WHERE civicrm_tag.name LIKE
"%Primary%"  OR civicrm_tag.name  LIKE "%Secondary%") AS derivedtable
ON derivedtable.id = civicrm_entity_tag.tag_id

 WHERE derivedtable.id IS NULL
  GROUP BY civicrm_contact.id

but I can't get it right. :(

I think the trick is to use a derived table for the join. That was his trick.