query.sql

SELECT e.id, (
    SELECT CASE periodicity 
        WHEN 'daily' THEN DATEDIFF('2011-01-08', s.starts_at) <= s.occurrences -1
        WHEN 'weekly' THEN DAYOFWEEK('2011-01-08') = DAYOFWEEK(s.starts_at) AND '2011-01-08' < ADDDATE(s.starts_at, INTERVAL s.occurrences -1 WEEK) 
        WHEN 'bi-weekly' THEN DATEDIFF('2011-01-08', s.starts_at) MOD 14 = 0 AND '2011-01-08' < ADDDATE(s.starts_at, INTERVAL s.occurrences -1 WEEK) 
        WHEN 'monthly' THEN DAY('2011-01-08') = DAY(s.starts_at) AND '2011-01-08' < ADDDATE(s.starts_at, INTERVAL s.occurrences -1 MONTH)
        WHEN 'yearly' THEN DAY('2011-01-08') = DAY(s.starts_at) AND MONTH('2011-01-08') = MONTH(s.starts_at) AND '2011-01-08' < ADDDATE(s.starts_at, INTERVAL s.occurrences - 1 YEAR)
    END) AS matches 
FROM events AS e
INNER JOIN schedules AS s ON (s.event_id = e.id)
HAVING matches = 1;