MySQL JOIN Conditional Query

Question

What the query does: It gets the required data, such as event title, post name, weekday, event start time, event end time for the upcoming events based on current time.

I am passing the weekday value from PHP Date('w') and Current time as well.

PROBLEM: I want it to match the start_hour only for current weekday (5 in this case). Where I have AND wp_wcs3_schedule.start_hour > '09:00:00' Need to restrict this condition to be applied to current weekday which is "5" in this case.

SELECT wp_posts.ID, wp_posts.post_title, wp_posts.post_name, wp_wcs3_schedule.weekday, 
    wp_wcs3_schedule.start_hour, wp_wcs3_schedule.end_hour
FROM wp_wcs3_schedule
INNER JOIN wp_posts
WHERE wp_wcs3_schedule.class_id = wp_posts.ID
AND wp_wcs3_schedule.weekday >=5
AND wp_wcs3_schedule.start_hour >  '09:00:00'
AND wp_posts.post_title NOT LIKE  '%squash%'
AND wp_posts.post_title NOT LIKE  '%sal%'
ORDER BY wp_wcs3_schedule.weekday, wp_wcs3_schedule.start_hour ASC LIMIT 0,3

Answer 1

You can do this easliy using OR, so

AND ( wp_wcs3_schedule.weekday > 5 OR wp_wcs3_schedule.start_hour >  '09:00:00')

You already have >= 5 so .weekday will be 5 or above, and this then will only apply the start hour criteria to those with weekday == 5. This gives a final query of

SELECT wp_posts.ID, wp_posts.post_title, wp_posts.post_name, wp_wcs3_schedule.weekday, 
    wp_wcs3_schedule.start_hour, wp_wcs3_schedule.end_hour
FROM wp_wcs3_schedule
INNER JOIN wp_posts
WHERE wp_wcs3_schedule.class_id = wp_posts.ID
AND wp_wcs3_schedule.weekday >=5
AND ( wp_wcs3_schedule.weekday > 5 OR wp_wcs3_schedule.start_hour >  '09:00:00')
AND wp_posts.post_title NOT LIKE  '%squash%'
AND wp_posts.post_title NOT LIKE  '%sal%'
ORDER BY wp_wcs3_schedule.weekday, wp_wcs3_schedule.start_hour ASC LIMIT 0,3