Mysql query with HAVING COUNT clause

Question

So, I have a specific request that I am trying to configure a bit. The needs for the project have changed a bit, and I'm not sure how to approach this.

I have 3 tables - the main table, the "tags" table, and then the link table for linking tags to the master records. The wrinkle is that there is weight for this binding, and this is used to summarize the total weight of the tags associated with a particular Name record in the main table. In short, a master record can contain multiple tags, each with a different weight. The current query summarizes all the tag weights and arranges them for the total amount 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 

I have currently built this to accomplish this perfectly, where 2.1 is the tag identifier string I'm looking for 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 expand this for another use. Right now, the tag identifier, which is passed as a string, is subtractive. It finds only matches where both tag identifiers exist for a specific person identifier. If I wanted to pass another id line, where if the ANY of the user matches ANY of the tag identifiers from this line, followed by the current id subtraction line, plus sum the weight of these tags, how can I do this

Answer 1

I believe the correct having for your request is:

 HAVING COUNT(DISTINCT linkage.tag_id)=2 

Your version finds exactly 1 tag.

In the next version of the request, tags 3 and 4 are 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 in the count(distinct) clause.