Why is this query failing?

Question

When I run this query:

SELECT m.tag_id, m.product_id, count(m.product_id) as dups
FROM product_tag_map m
INNER JOIN category_tag_map s ON s.tag_id = m.tag_id
INNER JOIN cart_product p ON m.product_id = p.product_id
WHERE m.product_id = p.product_id
AND s.category_id =508
AND s.include =1
AND dups = (SELECT count(s.category_id) as cats FROM category_tag_map s WHERE s.category_id =508)
GROUP BY m.product_id
ORDER BY dups DESC

I get this error:

1054 - Unknown column 'dups' in 'where clause'

If I take dups out of the where clause I get this:

tag_id  product_id  dups
2   7038    2
2   1294    1

I guess I can not use the dups field in the where clause because that field is being generated on the fly. So how can I do what I am trying to do then?

Answer 1

As you are filtering an aggregate you would need to use Having. MySQL does actually allow you to reference column aliases in a having clause (most other RDBMS's don't allow this)

SELECT m.tag_id, m.product_id, count(m.product_id) as dups
FROM product_tag_map m
INNER JOIN category_tag_map s ON s.tag_id = m.tag_id
INNER JOIN cart_product p ON m.product_id = p.product_id
WHERE m.product_id = p.product_id
AND s.category_id =508
AND s.include =1
GROUP BY m.product_id
HAVING dups = (SELECT count(s.category_id) as cats FROM category_tag_map s WHERE s.category_id =508)
ORDER BY dups DESC

Answer 2

dups is just an alias for the result of count().. i'm not sure if you can use count(m.product_id)=(SELECT ...) in the WHERE clause, if it doesn't i'd say that you could test it in the HAVING clause