[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
> LEFT OUTER
> 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 )
> GROUP
> 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