验证mysql行是否与另一行冲突

I'm making a resource reservation system on the school I work in. Resources are reserved for only 40min (which is what the class lasts).

Resources are Split into 4 categories:

1- Preschool

2- Elementary

3- High School, First Floor

4- High School, Second Floor

Schedules are devided into different time spans:

1- nursery

2- pre-kinder

3- kinder

4- elementary (1st grade - 3rd grade)

5- elementary (4th grade - 6th grade)

6- High School (7th grade - 9th grade)

7- High School (10th grade and up)

Sometimes, there's a scenario where 1st grade to 3rd grade students are in break, and 4th grade to 6th grade students are in a class. Break lasts 15min and classes 40min. Sometimes, there are conflicts on schedules where if the user is allowed to reserve a resource, that resource would be in use for at least another 10 minutes by another user, this happens on both preschool, elementary and high school.

My DB reservation table structure is as follows:

I have two fields that I think will help me determine whether making a reservation will make a conflict or not: RESERVATIONDUEDATE and RESERVATIONEXPIREDATE which are both datetime and have the info I need. I'm having trouble picturing how would I make a query to verify if there will be a conflict or not.

So far, I have a function with the query that looks like this:

     SELECT T1.SCHEDULE_START, T1.SCHEDULE_END
FROM reservation T4
JOIN schedule_intermediate T1 ON T4.SCHEDULEID_GENERATED = T1.SCHEDULEID_GENERATED
JOIN schedule_type T2 ON T1.SCHEDULE_TYPE_ID = T2.SCHEDULE_TYPE_ID
JOIN schedule T3 ON T1.SCHEDULEID = T3.SCHEDULEID
WHERE (
SELECT COUNT( * ) 
FROM reservation R
INNER JOIN schedule_intermediate SI ON R.SCHEDULEID_GENERATED = SI.SCHEDULEID_GENERATED
WHERE R.RESOURCEID =456
AND SI.SCHEDULE_TYPE_ID =4
AND R.RESERVATIONDUEDATE !=  '2014-08-06 07:35:00'
AND R.RESERVATIONEXPIREDATE !=  '2014-08-06 08:35:00'
AND(( SI.SCHEDULE_START < T1.SCHEDULE_START AND SI.SCHEDULE_END > T1.SCHEDULE_START ) 
    OR ( SI.SCHEDULE_START < T1.SCHEDULE_END AND SI.SCHEDULE_END > T1.SCHEDULE_END ))
) = 0

That query intends to select only the available schdule periods of time where the RESOURCEID, SCHEDULE_TYPE_ID, 'SCHEDULE_START' and 'SCHEDULE_END' are not the same as the info provided in the query.

At this point, I'm not even sure if the query is structured correctly, as the result of this query is:

And what I really want are the periods of time where those properties are not the same as the ones provided.

I'm trying to get my head around what you need. Try this... do a search similar to what your doing...

WHERE R.RESOURCEID =456
AND SI.SCHEDULE_TYPE_ID =4
AND R.RESERVATIONDUEDATE >=  '2014-08-06 07:35:00'
AND R.RESERVATIONEXPIREDATE <=  '2014-08-06 08:35:00'

If your query returns 0 rows, there will be no clashes.

At the moment, your query returns that row because the Schedule_Start is 07:50 which does not equal 07:35:00 ?

Also, is this for recurring sessions, and if so.. how are you managing this in your DB in the timestamp format?