CODEWITHSUNDEEP
mysqlsqldatabase
jpea
jpea

Reputation: 3079

mysql query with HAVING COUNT clause

So, I have a particular query that I'm trying to tweak a bit. The needs for the project changed a bit and I'm not sure how to approach this.

I have 3 tables - a main table, a "tags" table and then a linking table to tie the tags to the main entries. The wrinkle is that there is a weight given to that linkage and that is used to sum the total weight of the tags linked to a particular Name entry in the main table. In short, a main entry might have multiple tags on it, each with a different weight. The current query sums all of the tag weights and orders them by the total sum of all tags. 

UID  | Name 
-----------------
123  | Robert


UID  | Tag_Name 
-----------------
1    | Name_One
2    | Name_Two


Tag_ID  | Name_ID    | Weight
-----------------------------
2       | Name_One   |  2
1       | Name_Two   |  3
1       | Name_One   |  5

Currently, I've built this that accomplishes this fine where 2,1 is a string of the tag id's that I'm looking to match:

SELECT person.id, 
   SUM(linkage.weight) AS total_weight 
   FROM (person) 
   INNER JOIN linked_tags AS linkage 
   ON linkage.track_id = person.id AND linkage.tag_id IN (2,1) 
   GROUP BY person.id 
   HAVING COUNT(DISTINCT linkage.tag_id)=1 
   ORDER BY total_weight DESC

I want to extend this for another use. Right now, the tag id's that are passed in as a string are subtractive. It only finds matches where both tag id's exist for a certain person id. If I wanted to pass another string of id's in, where if ANY person id's match ANY of the tag id's out of that string, followed by the current subtractive string of id's, plus sum the weight of those tags, how might I go about it?

Upvotes: 4

Views: 170

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269733

I believe the correct having clause for your query is:

HAVING COUNT(DISTINCT linkage.tag_id)=2

Your version finds exactly 1 tag.

The following version of the query has tags 3 and 4 being optional:

   SELECT person.id, SUM(linkage.weight) AS total_weight 
   FROM person INNER JOIN
        linked_tags AS linkage 
        ON linkage.track_id = person.id AND
           linkage.tag_id IN (2, 1, 3, 4) 
   GROUP BY person.id 
   HAVING COUNT(DISTINCT case when linkage.tag_id in (1, 2) then linkage.tag_id end) = 2
   ORDER BY total_weight DESC ;

The big difference is the use of the case statement in the count(distinct) clause.

Upvotes: 2

Related Questions