[thelist] Stuck on SQL

[r937]

fred, first of all, thanks for the kind words

in the original query that you posted --

  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
  LEFT OUTER
    JOIN civicrm_tag
      ON civicrm_tag.id = civicrm_entity_tag.tag_id
  GROUP
      BY civicrm_contact.id;

you can actually remove the second join and get the same results

;o)

so if this query produces the results you want, then we must leave this 
SELECT clause as is, because it counts the tags the contacts ~do~ have, and 
we want to keep showing that

the qualification of which contacts survive the new filter criterion can 
easily be done with a subquery in the WHERE clause

chris nailed it when he said "How about counting the number of tags, then 
using the HAVING clause to just select those with 0 tags"

here's his query --

  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
  LEFT OUTER
    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

chris, you could've omitted COUNT(civicrm_entity_tag.tag_id) from the SELECT 
clause, because you know what it's going to be, right?

note that here we do have to extend the joins to the tags table, in order to 
perform the test on the tag name

however, WHERE is wrong, it has to be AND

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

;o)



rudy
http://simply-sql.com/