using CASE in the WHERE clause

Question

simplified version of my query  SELECT   FROM logs  WHERE pw  correct  AND CASE WHEN id lt 800 THEN success 1 ELSE END  AND YEAR timestamp  2011    this doesn t work  What i m trying to do is to add in success 1 only for rows with id lt 800  else ignore this check   how do i write this  thanks   edit  to clarify  this what the table looks like   id    pw        success   --------------------------  700   correct   1          710   correct   1          900   correct   NULL       999   correct   0           I m trying to return all the rows  the column pw cannot be ignored

User · Answer

This is working Oracle example but it should work in MySQL too.

You are missing smth - see IN after END Replace 'IN' with '=' sign for a single value.

SELECT empno, ename, job
  FROM scott.emp
 WHERE (CASE WHEN job = 'MANAGER' THEN '1'  
         WHEN job = 'CLERK'   THEN '2' 
         ELSE '0'  END) IN (1, 2)

User · Answer

You can transform logical implication A   gt  B to NOT A or B  This is one of the most basic laws of logic  In your case it is something like this   SELECT   FROM logs  WHERE pw  correct  AND  id gt  800 OR success 1    AND YEAR timestamp  2011   I also transformed NOT id lt 800 to id gt  800  which is also pretty basic

User · Answer

You don t have to use CASE   WHEN  you could use an OR condition  like this   WHERE   pw  correct    AND  id gt  800 OR success 1     AND YEAR timestamp  2011   this means that if id lt 800  success has to be 1 for the condition to be evaluated as true  Otherwise  it will be true anyway   It is less common  however you could still use CASE WHEN  like this   WHERE   pw  correct    AND CASE WHEN id lt 800 THEN success 1 ELSE TRUE END    AND YEAR timestamp  2011   this means  return success 1  which can be TRUE or FALSE  in case id lt 800  or always return TRUE otherwise

User · Answer

SELECT   FROM logs WHERE pw  correct    AND CASE           WHEN id lt 800 THEN success 1           ELSE 1 1       END   AND YEAR TIMESTAMP  2011

[mysql] using CASE in the WHERE clause

Examples related to mysql

Examples related to sql