How do I use properly CASE WHEN in MySQL

Question

Here is a demo query  notice it is very simple  Fetches only where base price is 0  And still  it chooses the condition 3   SELECT    CASE course enrollment settings base price     WHEN course enrollment settings base price   0      THEN 1     WHEN course enrollment settings base price lt 101      THEN 2     WHEN course enrollment settings base price gt 100 AND                          course enrollment settings base price lt 201 THEN 3         ELSE 6    END AS  calc base price      course enrollment settings base price FROM     course enrollment settings WHERE course enrollment settings base price   0   base price is decimal 8 0   When run this on my DB  I get      3 0   3 0   3 0   3 0   3 0

User · Answer

CASE case value     WHEN when value THEN statements      WHEN when value THEN statements      ELSE statements END    Or   CASE WHEN  lt search condition gt  THEN statements  WHEN  lt search condition gt  THEN statements   ELSE statements END    here CASE is an expression  in 2nd scenario  search condition will evaluate and if no search condition is equal then execute else   SELECT    CASE course enrollment settings base price     WHEN course enrollment settings base price   0      THEN 1   should be   SELECT    CASE      WHEN course enrollment settings base price   0      THEN 1

User · Answer

Remove the course enrollment settings base price immediately after CASE   SELECT    CASE     WHEN course enrollment settings base price   0      THEN 1             END   CASE has two different forms  as detailed in the manual  Here  you want the second form since you re using search conditions

User · Answer

There are two variants of CASE  and you re not using the one that you think you are   What you re doing  CASE case value     WHEN when value THEN statement list      WHEN when value THEN statement list           ELSE statement list  END CASE   Each condition is loosely equivalent to a if  case value    when value   pseudo-code    However  you ve put an entire condition as when value  leading to something like   if  case value     case value  gt  100     Now   case value  gt  100  evaluates to FALSE  and is the only one of your conditions to do so  So  now you have   if  case value    FALSE    FALSE converts to 0 and  through the resulting full expression if  case value    0  you can now see why the third condition fires   What you re supposed to do  Drop the first course enrollment settings so that there s no case value  causing MySQL to know that you intend to use the second variant of CASE   CASE     WHEN search condition THEN statement list      WHEN search condition THEN statement list           ELSE statement list  END CASE   Now you can provide your full conditionals as search condition   Also  please read the documentation for features that you use

User · Answer

SELECT    CASE      WHEN course enrollment settings base price   0      THEN 1     WHEN course enrollment settings base price gt 0 AND            course enrollment settings base price lt  100     THEN 2     WHEN course enrollment settings base price gt 100 AND             course enrollment settings base price lt 201      THEN 3         ELSE 6    END AS  calc base price      course enrollment settings base price FROM     course enrollment settings WHERE course enrollment settings base price   0

User · Answer

CASE course enrollment settings base price is wrong here  it should be just CASE  SELECT  CASE  WHEN course enrollment settings base price   0      THEN 1  WHEN course enrollment settings base price lt 101      THEN 2  WHEN course enrollment settings base price gt 100 AND                       course enrollment settings base price lt 201 THEN 3      ELSE 6  END AS  calc base price    course enrollment settings base price  FROM    course enrollment settings  WHERE course enrollment settings base price   0    Some explanations  Your original query will be executed as    SELECT  CASE 0 WHEN 0 0 THEN 1 -- condition evaluates to 1  then 0  from CASE 0 compares to 1 - false WHEN 0 lt 1 THEN 2 -- condition evaluates to 1 then 0  from CASE 0 compares to 1 - false WHEN 0 gt 100 and 0 lt 201 THEN 3 -- evaluates to 0  then 0  from CASE 0 compares to 0 - true ELSE 6        it s why you always get 3

User · Answer

I think part of it is that you re stating the value you re selecting after CASE  and then using WHEN x   y syntax afterward  which is a combination of two different methods of using CASE   It should either be  CASE X   WHEN a THEN       WHEN b THEN       or  CASE   WHEN x   a THEN       WHEN x   b THEN

[mysql] How do I use properly CASE..WHEN in MySQL

Examples related to mysql

Examples related to sql

Examples related to conditional

Examples related to switch-statement

Examples related to case