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

Simon at The Access Group · Accepted Answer · 2014-03-07 13:39:10Z

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

answered Mar 7, 2014 at 13:39

Simon at The Access Group

7,0251 gold badge25 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jade Over a year ago

I was using AND instead of OR which was not making logic I guess

Simon at The Access Group Over a year ago

The parantheses are also important there. Without them unintended results would occur.

Collectives™ on Stack Overflow

MySQL JOIN Conditional Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related