MySQL where clause targeting dependent subquery

Question

I work with entity-attribute-value database model.

My product have many attribute_varchars and attribute_varchars have one attribute. An attribute has many attribute_varchars and an attribute varchar has one product. Same logic apply to attribute_decimals and attribute_texts.

Anyway, i have the following query and i would like to filter the result using a where clause

SELECT 
    products.id,
    (select value from attribute_texts    where product_id = products.id and attribute_id = 1) 
        as description,
    (select value from attribute_varchars where product_id = products.id and attribute_id = 2) 
        as model,
    (select value from attribute_decimals where product_id = products.id and attribute_id = 9) 
        as rate,
    (select value from attribute_varchars where product_id = products.id and attribute_id = 20) 
        as batch
FROM products
WHERE products.status_id <> 5

I would like to add a where rate > 5

I tried but I get the following error : Unknown column 'rate' in 'where clause'. I tried adding an alias to the value and to the table of the value but nothing seems to work.

Answer 1

In MySQL, you can do:

having rate > 5

MySQL has extended the having clause so it can work without a group by. Although questionable as a feature, it does allow you to reference aliases in the select clause without using a subquery.