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.

Answer 1

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.

Answer 2

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