Anonymous Anonymous - 1 month ago 9
SQL Question

how to filter floor calculated value on select with joins where it is not null?

I have query with joins (in magento) :

SELECT FLOOR(rating.rating_summary/20) AS `rating_value`,
COUNT(*) AS `count`
FROM `catalog_product_flat_3` AS `e`
INNER JOIN `catalogsearch_result` AS `search_result` ON search_result.product_id=e.entity_id
AND search_result.query_id='218'
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id
AND price_index.website_id = '1'
AND price_index.customer_group_id = 0
INNER JOIN `review_entity_summary` AS `rating` ON rating.entity_pk_value = e.entity_id
AND rating.store_id = 0;


It is required to filter out rows with NULL in
rating_value
or
count < 1


+--------------+-------+
| rating_value | count |
+--------------+-------+
| NULL | 0 |
+--------------+-------+
1 row in set (0,00 sec)


but for some reason where i try
WHERE count > 0
or
rating_value IS NOT NULL
it saids that subject column
rating_value
,
count
is not exists, what do I do wrong ?

Thank you!

Answer

Correct. A column alias defined in a SELECT cannot be used in the WHERE. You need a subquery or to repeat the expression:

WHERE FLOOR(rating.rating_summary/20) IS NOT NULL

This is equivalent to:

WHERE rating.rating_summary IS NOT NULL

The count needs to go in a HAVING clause, which I would write as:

HAVING count = 0 OR rating_value IS NOT NULL

I would also expect your query to have a GROUP BY clause, which it seems to be missing.

So I suspect you want:

SELECT FLOOR(rating.rating_summary/20) AS `rating_value`,
       COUNT(*) AS `count`
FROM `catalog_product_flat_3` pf INNER JOIN
     `catalogsearch_result` sr
      ON sr.product_id = pf.entity_id AND
         sr.query_id = 218 INNER JOIN
      `catalog_product_index_price` pip
      ON pip.entity_id = pf.entity_id AND
        pip.website_id = 1 AND
        pip.customer_group_id = 0 INNER JOIN
      `review_entity_summary` r
     ON r.entity_pk_value = pf.entity_id AND r.store_id = 0
GROUP BY FLOOR(r.rating_summary/20)
HAVING `count` = 0 OR rating_value IS NULL;