SQL WHEREing on a different table's COUNT

Question

So, I want to apply a WHERE condition to a field assigned by a COUNT() AS clause. My query currently looks like this:

SELECT new_tags.tag_id
       , new_tags.tag_name
       , new_tags.tag_description
       , COUNT(DISTINCT new_tags_entries.entry_id) AS entry_count 
FROM (new_tags) 
JOIN new_tags_entries ON new_tags_entries.tag_id = new_tags.tag_id 
WHERE `new_tags`.`tag_name` LIKE '%w' 
AND `entry_count` < '1' 
GROUP BY new_tags.tag_id ORDER BY tag_name ASC

The bit that's failing is the entry_count in the WHERE clause - it doesn't know what the entry_count column is. My table looks like this:

new_tags {
    tag_id INT
    tag_name VARCHAR
}

new_tags_entries {
    tag_id INT
    entry_id INT
}

I want to filter the results by the number of distinct entry_ids in new_tags_entries that pertain to the tag ID.

Make sense?

Thanks in advance.

Answer 1

An inner join will never have a count of less than 1. Perhaps a left join and IS NULL would help. That, or using SUM() instead.

Answer 2

SELECT 
   new_tags.tag_id, new_tags.tag_name, 
   new_tags.tag_description, 
   COUNT(DISTINCT new_tags_entries.entry_id) AS entry_count 
FROM (new_tags) 
LEFT JOIN new_tags_entries ON new_tags_entries.tag_id = new_tags.tag_id  
WHERE `new_tags`.`tag_name` LIKE '%w'  
GROUP BY new_tags.tag_id ORDER BY tag_name ASC
HAVING `entry_count` < '1'

Answer 3

To filter on aggegated values use the HAVING clause...

SELECT  
   new_tags.tag_id, new_tags.tag_name,  
   new_tags.tag_description,  
   COUNT(DISTINCT new_tags_entries.entry_id) AS entry_count  
FROM (new_tags)  
JOIN new_tags_entries ON new_tags_entries.tag_id = new_tags.tag_id   
WHERE `new_tags`.`tag_name` LIKE '%w'   
GROUP BY new_tags.tag_id  
HAVING COUNT(DISTINCT new_tags_entries.entry_id)  < '1' 
ORDER BY tag_name ASC

Answer 4

Although APC's answer will be syntactically correct, if the problem you are trying to solve is indeed: "Find me all new_tags that do not have any news_tags_entries", then the query with INNER JOIN and GROUP BY and HAVING will not yield the correct result. In fact, it will always yield the empty set.

As Ignacio Vazques Abrahams pointed out, a LEFT JOIN will work. And you don't even need the GROUP BY / HAVING:

SELECT    news_tags.*
FROM      news_tags
LEFT JOIN news_tags_entries 
ON        news_tags.tag_id = news_tags_entries.tag_id
WHERE     news_tags_entries.tag_id IS NULL

(Of course, you can still add GROUP BY and HAVING if you are interested to know how many entries there are, and not just want to find news_tags with zero news_tags_entries. But the LEFT JOIN from news_tags to news_tags_entries needs to be there or else you'll lose the news_tags that have no corresponding items in news_tags_items)

Another, more explicit way to solve the "get me all x for which there is no y" is a correlated NOT EXISTS solution:

SELECT    news_tags.*
FROM      news_tags
WHERE NOT EXISTS (
          SELECT NULL
          FROM   news_tags_entries
          WHERE  news_tags_entries.tag_id = news_tags.tag_id
          )

Although nice and explicit, this solution is typically shunned in MySQL because of the rather bad subquery performance