Getting syntax error when using CASE to check if column is more than 0, else use other column-CodePudding

I am trying to use the CASE function to check if the discount_price column is more than 0, if so then select everything where discount_price >= 60 else select everything where price >= 60.

But for some reason I get a syntax error.

The error:

Something is wrong with the used syntax ')

My query:

SELECT * FROM `product_variants` WHERE 
(CASE WHEN `discount_price` > 0 THEN (SELECT * FROM product_variants WHERE discount_price >= 60) ELSE (SELECT * FROM product_variants WHERE price >= 60))

What am I doing wrong? I am using mysql

CodePudding user response：

I would just phrase this using a single query and WHERE clause:

SELECT *
FROM product_variants
WHERE discount_price >= 60 OR price >= 60;

CodePudding user response：

I think you don't need a case statement, everything could be in one query with some logic:

SELECT *
FROM product_variants
WHERE (discount_price > 0 AND discount_price >= 60)
OR (discount_price <= 0 AND price >= 60)

Assuming discount_price cannot be negative, the second condition can be (discount_price = 0 AND price >= 60).