MySQL IF ELSEIF in select query

Question

I am attempting to select various prices of a product based on the quantity that the user chooses. 

Following is the query I am working on (it has a syntax error):

 select id, 
    (SELECT 
    IF(qty_1<='23',price,1)
    ELSEIF(('23'>qty_1 && qty_2<='23'),price_2,1)
    ELSEIF(('23'>qty_2 && qty_3<='23'),price_3,1)
    ELSEIF('23'>qty_3,price_4,1)
    END IF) as total 
 from product;

Answer 1

You have what you have used in stored procedures like this for reference, though they are not intended to be used as you have now. You can use IF, but a Case statement is better for eyes. Like this:

select id, 
    (
    CASE 
        WHEN qty_1 <= '23' THEN price
        WHEN '23' > qty_1 && qty_2 <= '23' THEN price_2
        WHEN '23' > qty_2 && qty_3 <= '23' THEN price_3
        WHEN '23' > qty_3 THEN price_4
        ELSE 1
    END) AS total
 from product;

This seems cleaner. I think you do not need the inner SELECT anyway.

Want to become an expert in SQL? Join the SQL course fast!

For more details on MySQL, refer to the below MySQL Tutorial video.