Conditional SELECT MySQL

Question

Don't know if this is possible, but I'd like to select records based on the field value of recur_type, where the 'm' is the day of the week. If it's a weekly recurring event, I need to make sure this is a day it recurs on, otherwise, I want to return all days. however, I'm getting an empty result set:

SELECT *
FROM wp_fun_bec_events
WHERE start_date <= '2009-10-12'
AND ( end_date >= '2009-10-12'
   OR (recur_end > '0' AND recur_end >= '2009-10-12' ) )
AND ('m' IN (
   CASE WHEN 'recur_type' = 'weekly'
    THEN recur_days
    ELSE 's/m/t/w/r/f/a'
   END ) )
ORDER BY start_date, start_time

Any ideas??

Answer 1

It might have to do with your IN clause. The only way that will ever work is if 'recur_days' is equal to 'm'. It will never work in the 'else' since 'm' <> 's/m/t/w/r/f/a' (unless 'm' is the name of a column and those apostrophes are actually backticks. If so, let me know and I'll try again).

Answer 2

Hmm. Maybe try:

SELECT *
FROM wp_fun_bec_events
WHERE start_date <= '2009-10-12'
AND ( end_date >= '2009-10-12'
   OR (recur_end > '0' AND recur_end >= '2009-10-12' ) )
AND ((
   CASE WHEN 'recur_type' = 'weekly'
    THEN recur_days
    ELSE 's/m/t/w/r/f/a'
   END ) LIKE '%m%' )
ORDER BY start_date, start_time

Not entirely sure about the MySQL syntax, though.