SQL having with count - not working

Question

I have a taggings table. The relevant rows from that table for this questions are: tag_id and taggable_id. I want to select a taggable_id that has all the tag_id's. But this query comes empty:

SELECT taggable_id, 
       tag_id 
FROM   taggings 
WHERE  tag_id IN( 73, 76 ) 
       AND taggable_id = 3238 
GROUP  BY tag_id 
HAVING Count(tag_id) = 2

Now, when I use the query withut the count, I get 2 rows as a result:

SELECT taggable_id, 
       tag_id 
FROM   taggings 
WHERE  tag_id IN( 73, 76 ) 
       AND taggable_id = 3238 

Why?

Answer 1

You need to change the GROUP BY column to group by the taggable_id and you need to change the having clause to count distinct tag_id's:

SELECT taggable_id
FROM   taggings 
WHERE  tag_id IN( 73, 76 ) 
GROUP  BY taggable_id 
HAVING Count(DISTINCT tag_id) = 2

See SQL Fiddle with Demo. This type of query is known as relational division.

Your current query was not working because you were grouping by the tag_id instead of the taggable_id.