Menu
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.
408
CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.
You can use a CASE expression in any statement or clause that accepts a valid expression. For example, you can use the CASE expression in statements such as SELECT , UPDATE , or DELETE , and in clauses like SELECT , WHERE , HAVING , and ORDDER BY .
The case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By, and Group By clause. It can be used in the Insert statement as well.
The SQL WHERE clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table. You should use the WHERE clause to filter the records and fetching only the necessary records.
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.
191
SELECT * FROM logs WHERE pw='correct' AND CASE WHEN id<800 THEN success=1 ELSE 1=1 END AND YEAR(TIMESTAMP)=2011 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
