using CASE in the WHERE clause

Question

simplified version of my query

SELECT * FROM logs WHERE pw='correct' AND CASE WHEN id<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<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.

Maybe it can help you: stackoverflow.com/questions/14608245/… — cheesemacfly
– cheesemacfly, Commented Jan 30, 2013 at 21:56

fthiella · Accepted Answer · 2013-01-30 22:07:25Z

You don't have to use CASE...WHEN, you could use an OR condition, like this:

WHERE pw='correct' AND (id>=800 OR success=1) AND YEAR(timestamp)=2011

this means that if id<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<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<800, or always return TRUE otherwise.

Rikin Patel · Accepted Answer · 2014-03-25 10:46:20Z

36

SELECT * FROM logs WHERE pw='correct' AND CASE WHEN id<800 THEN success=1 ELSE 1=1 END AND YEAR(TIMESTAMP)=2011

9,4738 gold badges74 silver badges80 bronze badges

answered Mar 25, 2014 at 10:18

4,1941 gold badge35 silver badges24 bronze badges

3 Comments

Upvoted because the aligment and indentation make the code more understandable.

CASE is the way!

ELSE 1=1 is important! :)

Rikin Patel · Accepted Answer · 2014-03-25 10:48:21Z

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)

Rok Kralj · Accepted Answer · 2013-01-30 21:58:25Z

You can transform logical implication A => 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>=800 OR success=1) AND YEAR(timestamp)=2011

I also transformed NOT id<800 to id>=800, which is also pretty basic.