MySQL IF ELSEIF in select query

Question

I m trying to select different prices of a product based on the quantity that user chooses  This is the query I m working on  it has a syntax error     select id        SELECT      IF qty 1 lt   23  price 1      ELSEIF   23  gt qty 1  amp  amp  qty 2 lt   23   price 2 1      ELSEIF   23  gt qty 2  amp  amp  qty 3 lt   23   price 3 1      ELSEIF  23  gt qty 3 price 4 1      END IF  as total   from product

User · Accepted Answer

You have what you have used in stored procedures like this for reference, but they are not intended to be used as you have now. You can use IF as shown by duskwuff. 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 looks cleaner. I suppose you do not require the inner SELECT anyway..

User · Answer

I found a bug in MySQL 5 1 72 when using the nested if   functions      the value of column variables  e g  qty 1  is blank inside the second if    rendering it useless  Use the following construct instead   case    when qty 1 lt   23  then price   when  23  gt qty 1  amp  amp  qty 2 lt   23  then price 2   when  23  gt qty 2  amp  amp  qty 3 lt   23  then price 3   when  23  gt qty 3 then price 4   else 1 end

User · Answer

IF   in MySQL is a ternary function  not a control structure -- if the condition in the first argument is true  it returns the second argument  otherwise  it returns the third argument  There is no corresponding ELSEIF   function or END IF keyword   The closest equivalent to what you ve got would be something like   IF qty 1 lt   23   price    IF  23  gt qty 1  amp  amp  qty 2 lt   23   price 2      IF  23  gt qty 2  amp  amp  qty 3 lt   23   price 3        IF  23  gt qty 3  price 4  1                The conditions don t all make sense to me  it looks as though some of them may be inadvertently reversed    but without knowing what exactly you re trying to accomplish  it s hard for me to fix that

User · Answer

As per Nawfal s answer  IF statements need to be in a procedure  I found this post that shows a brilliant example of using your script in a procedure while still developing and testing  Basically  you create  call then drop the procedure   https   gist github com jeremyjarrell 6083251

User · Answer

For your question     SELECT id      IF qty 1  lt    23   price     IF   23   gt  qty 1  amp  amp  qty 2  lt    23    price 2     IF   23   gt  qty 2  amp  amp  qty 3  lt    23    price 3     IF   23   gt  qty 2  amp  amp  qty 3 lt   23    price 3     IF  23   gt  qty 3  price 4  1      as total  FROM product    You can use the if - else control structure or the IF function in MySQL   Reference  http   easysolutionweb com sql-pl-sql how-to-use-if-and-else-in-mysql

[mysql] MySQL IF ELSEIF in select query

Examples related to mysql

Examples related to if-statement