[thelist] Stuck on SQL

[Matt Warden]

On Fri, Jul 24, 2009 at 7:10 AM, Fred Jones<fredthejonester at gmail.com> wrote:
> I have this:
>
> 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
>  GROUP BY civicrm_contact.id;
>
> which is good. I get results like
>
> 1  4
> 2  0
> 3  0
> 5  3
>
> What I really want, however, is to get all contacts which do NOT have
> a tag where the tag name is:
>
> civicrm_tag.name LIKE "%Primary%"  OR civicrm_tag.name  LIKE "%Secondary%"
>
> I don't care if he has zero tags or if he has 6 tags. I just care that
> he has zero tags where the tag has "Primary" or "Secondary" somewhere
> in its name.

So ask that question.

Untested...

SELECT civicrm_contact.id , COUNT(civicrm_entity_tag.tag_id)
FROM civicrm_contact
WHERE 1 > (
	select count(*)
	from civicrm_entity_tag
	inner join civicrm_tag
		on civicrm_tag.id = civicrm_entity_tag.tag_id
	where civicrm_entity_tag.contact_id =
		civicrm_contact.id
		AND civicrm_tag.name LIKE "%Primary%"
		OR civicrm_tag.name  LIKE "%Secondary%"
);

I didn't understand why you had left joins. Apologies if I missed a
part of your logic...

-- 
Matt Warden
Cincinnati, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.