Using SELECT IF in WHERE clause

Question

In the query below the SELECT statement has an IF case, if the price in table a is equal to 999999 than I set n_price to 0 otherwise the price stays the same.

What I want to know is would there be a simple way of using n_price (the updated value from the query) in the WHERE clause instead of a.price to carry out a calculation? if not what would be a simple way to retrieve the value in the WHERE clause

SELECT 
IF(a.price = 999999, 0, a.price)            AS n_price, 
l.quantity                                  AS n_quantity,
l.condition                                 AS n_condition

FROM b_products b  

LEFT JOIN a_products a ON b.id = a.id
LEFT JOIN l_products l ON b.id = l.id

WHERE 
AND ROUND((a.price/100*80) - l.price) >= (l.price/100*30)

Botond Balázs · Accepted Answer · 2012-11-21 03:16:34Z

2

I think this should work:

AND ROUND((IF(a.price = 999999, 0, a.price)/100*80) - l.price) >= (l.price/100*30)

You can't use the name n_price you defined in the select clause.

answered Nov 21, 2012 at 3:16

Botond Balázs

2,5001 gold badge25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using SELECT IF in WHERE clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related