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 · 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

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  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  AS total  from product    This looks cleaner  I suppose you do not require the inner SELECT anyway

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

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

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

[mysql] MySQL IF ELSEIF in select query

Examples related to mysql

Examples related to if-statement