[thelist] Stuck on SQL

Phil Turmel pturmel-webdev at turmel.org
Fri Jul 24 09:52:35 CDT 2009


Fred Jones wrote:
>> 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.

Hi Fred,

I think you are on the right track.  Try this (untested):

SELECT civicrm_contact.id
   FROM civicrm_contact
   LEFT JOIN (SELECT civicrm_entity_tag.contact_id, civicrm_tag.id
     FROM civicrm_entity_tag
     INNER JOIN civicrm_tag
       ON civicrm_entity_tag.tag_id = civicrm.tag.id) AS derivedtable
     ON derivedtable.contact_id = civicrm_contact.id
   WHERE derivedtable.id IS NULL

HTH,

Phil Turmel



More information about the thelist mailing list