[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