Error in displaying available classrooms..below is the SQL i used.

[mirz_ex01]

Hi everyone, I've been working on this module for quite sometimes. What I need to do is display available rooms/classrooms to end user according to selected date, start time and end time. The process requires checking in SCHEDULE table and BOOKING table. For example, there is a booking on 2012-03-06 at 15:00 until 17:00 for classroom I1-01 in BOOKING table and in SCHEDULE table, there is a regular class on Tuesday (2012-03-06) at 13:00 until 15:00.

Therefore, I1-01 is available at any other time except at 13:00 - 15:00 and 15:00 - 17:00. Here is my SQL and still needs some amendment to it.

Thank you for helping me

SELECT DISTINCT (t1.id_class) AS CLASS, t4.CLASS_NAME, t4.CLASS_CAPACITY, t4.CLASS_TYPE, t5.SEMESTER_NAME, t5.SEMESTER_ID
FROM schedule t1, class t4, semester t5
WHERE `t1`.`id_class`=`t4`.`CLASS_ID`
AND `t1`.`SEMESTER_ID`=`t5`.`SEMESTER_ID`
AND `t1`.`id_class`
NOT IN
(
SELECT t2.id_class
FROM schedule t2
WHERE t2.`DAY` = '$day'
AND (t2.time_start = '$time_start' OR t2.time_start > '$time_start')
AND (t2.time_end = '$time_end' OR t2.time_end > '$time_end' OR t2.time_end < '$time_end')
AND (t2.time_start = '$time_end' OR t2.time_start > '$time_end')
AND (t2.time_end = '$time_start' OR t2.time_end > '$time_start')
UNION ALL
SELECT t3.CLASS_ID
FROM booking t3
WHERE t3.`DATE`= '$request_date' AND t3.STATUS <> 0
AND (t3.START_TIME = '$time_start' OR t3.START_TIME > '$time_start')
AND (t3.END_TIME = '$time_end' OR t3.END_TIME > '$time_end' OR t3.END_TIME < '$time_end')
AND(t3.START_TIME = '$time_end' OR t3.START_TIME > '$time_end')
AND (t3.END_TIME = '$time_start' OR t3.END_TIME > '$time_start')
)
ORDER BY t1.id_class;