Mysql to check room exists for a specific date with time

Question

I have a control room exists between two points for a specific date.

I tried to execute two queries in order to run it some time rights, but when I select 10:00 AM to 12:00 PM, at the same time incorrect results do not return any records.

QUERY-1 :

SELECT 1 FROM `timetable_details` WHERE ( ((`td_from` <= '10:00:00') AND (`td_to` > '10:00:00')) OR ((`td_from` < '12:20:00') AND (`td_to` >= '12:20:00')) ) AND ((`td_room`='1') AND (`td_date`='2016-01-25')) 

QUERY-2 :

 SELECT 1 FROM `timetable_details` WHERE ( (`td_from` > '07:00:00') AND (`td_to` < '08:00:00') ) AND ((`td_room`='1') AND (`td_date`='2016-01-25')) 

I have a line number td_id = 4 , but not returned.

Thanks in advance.

Answer 1

You can use between with OR condition for both columns, as shown below:

 SELECT 1 FROM `timetable_details` WHERE (((((`td_from` BETWEEN '10:00:00' AND '12:30:00') OR (`td_to` BETWEEN '10:00:00' AND '12:30:00')) AND ((`td_room`='1') AND (`td_date`='2016-01-25') AND (`td_status` IS NULL))) AND (`td_from` <> '12:30:00')) AND (`td_to` <> '10:00:00')) 

Hope you get the correct result.