0

My query:

SELECT CASE WHEN a.merchant = 'apple' THEN ( a.price / 0.9 ) * 0.8 ELSE a.price END AS 'price', a.*, b.* FROM products a JOIN data b on ( a.post_id = b.post_id ) AND a.price BETWEEN 0 AND 4 ORDER BY a.score DESC LIMIT 0, 11

My querstion is, how to change the query so that the new discounted price (after CASE function) is compared in (BETWEEN) function.

Currently BETWEEN function compare old price.

Alon Eitan
  • 11,997
  • 8
  • 49
  • 58
lucas0912
  • 1
  • You can not use a column alias in the WHERE clause, so you will either have to simply repeat this whole CASE statement in your WHERE clause - or use HAVING for that part instead. (But be aware that the latter can have performance implications.) – CBroe Apr 09 '21 at 10:50
  • Assuming there is only one column named `score`, try; `SELECT * FROM (SELECT CASE WHEN a.merchant = 'apple' THEN ( a.price / 0.9 ) * 0.8 ELSE a.price END AS 'the_price', a.*, b.* FROM products a JOIN data b on ( a.post_id = b.post_id )) sq WHERE sq.the_price BETWEEN 0 AND 4 ORDER BY sq.score DESC LIMIT 0, 11`. Otherwise, you will have two columns named `price`. – Booboo Apr 09 '21 at 11:03

0 Answers0