MYSQL - Check conflicting time

Question

Currently, I have this table 'tbl_subloading' :

teacher_name  |  start_time   |  end_time  
de Guzman, J  |  08:00AM      |  10:00AM
Harris, M     |  07:00AM      |  09:00AM

What I want is my program to detect if schedule given to a certain teacher overlaps his/her existing schedule already. For example, 'de Guzman, J' could no longer have any class from 9am-11am since he is no longer available.

I tried this query that I saw coming from a different thread:

SELECT (COUNT(*) = 0) AS Available FROM tbl_subloading 
WHERE `teacher_name` = 'de Guzman, J' 
AND ( (start_time <= '07:00AM' AND end_time >= '07:00AM') 
OR (start_time <= '08:00AM' AND end_time >= '08:00AM'));

This query outputs 1 if he is available for that certain period, 0 if not. For example, if de Guzman, J is given a schedule from 12pm to 1pm, it outputs 1. But if de Guzman, J is given a schedule from 10am to 11am, it outputs 0 although he is already available for that period.

Is there any alternative you could do from this?

Thanks in advance.

Answer 1

You can user BETWEEN to simplify the query.

 SELECT (COUNT(*)) AS Available 
 FROM tbl_subloading 
 WHERE teacher_name = 'de Guzman, J' 
 AND ( (start_time BETWEEN '07:00AM' AND '08:00AM') OR (start_time BETWEEN '08:00AM' AND '09:00AM')) 

If de Guzman, J is not occupied between the given time periods, the output will be 0. Otherwise,based on the time slots you provide, it will be a positive integer value like 1,2. You can add many time slots by providing an OR condition.

Note that BETWEEN is inclusive.So your time slot starting and end values along with the values in between will be considered as well.