select - Mysql query to see if a booking will conflict with another booking. -

i have table stores bunch of books. customers can book appointments @ time during day. want make sure booking doesnt exist conflict new booking.

so want find booking on date , check start , end times dont overlap. tried doing sub select query bookings day , see if times overlapped didnt seem working.

here query using. (status , active other fields need). find relevant dates , see if 8am between results individual start/end times or 9.40am falls between start/end times.

select event_date, starttime, endtime, status, active  (select event_date, starttime, endtime, status, active bookings event_date='2013-07-21' , status != 'cancelled' , active !=0 limit 1) q1 ('8:00:00' between q1.starttime , q1.endtime) , ('9:40:00' between q1.starttime , q1.endtime) 

is best method use? thanks

to check if 1 range overlaps it's easier think first when won't overlap , reverse condition. it's obvious that:

two ranges (a,b) don't overlap if starts after b ends, or if ends before b starts.

turning sql following condition finding overlapping range:

... not ('8:00:00' > q1.endtime or '9:40:00' < q1.starttime) 

if want can rewrite as:

... q1.endtime > '8:00:00' , q1.starttime < '9:40:00' 

this way handle corner cases, 1 interval containing other.