Menu
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<101 THEN 2 WHEN course_enrollment_settings.base_price>100 AND course_enrollment_settings.base_price<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
477
MySQL CASE expression is a control flow structure that allows you to add if-else logic to a query. Generally speaking, you can use the CASE expression anywhere that allows a valid expression e.g., SELECT , WHERE and ORDER BY clauses. The CASE expression has two forms: simple CASE and searched CASE .
The CASE statement goes through conditions and return a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it will return the value in the ELSE clause.
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.
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.
154
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
