[thelist] Stuck on SQL

Fred Jones fredthejonester at gmail.com
Sat Jul 25 15:39:09 CDT 2009

> fred, first of all, thanks for the kind words


> however, WHERE is wrong, it has to be AND

Ah, that's the trick this time. Yes, I don't think I ever would have
thought of that. :)

> this query will identify all of those contacts for whom you want results
> returned by the original query, so we push it down a level of nesting, and
> make it a subquery in the WHERE clause...
>  SELECT civicrm_contact.id
>       , COUNT(civicrm_entity_tag.tag_id)
>    FROM civicrm_contact
>    JOIN civicrm_entity_tag
>      ON civicrm_entity_tag.contact_id = civicrm_contact.id
>   WHERE civicrm_contact.id IN
>         ( SELECT civicrm_contact.id
>             FROM civicrm_contact
>           LEFT OUTER
>             JOIN civicrm_entity_tag
>               ON civicrm_entity_tag.contact_id = civicrm_contact.id
>           LEFT OUTER
>             JOIN civicrm_tag
>               ON civicrm_tag.id = civicrm_entity_tag.tag_id
>              AND (
>                  civicrm_tag.name LIKE "%Primary%"
>               OR civicrm_tag.name LIKE "%Secondary%"
>                  )
>           GROUP
>               BY civicrm_contact.id
>           HAVING COUNT(civicrm_entity_tag.tag_id) = 0  )
>      BY civicrm_contact.id
> let me know if that works

Your basic query works, meaning the subquery above, and is actually
all I need for the job at hand--the full query is not quite as I
originally published--it's part of a system, but anyhow your solution
is what I need.

Your full solution, i.e. the whole query quote above, doesn't work for
me--it took up my CPU for a few minutes so I restarted MySQL. No
matter, however, the subquery is the answer.

Thank you very much.

More information about the thelist mailing list