mDQUWe8c

1TABLE 'schedule'
2   room        sched_start      sched_end
3     1           09:00:00       10:00:00
4     1           11:00:00       12:00:00
5     2           07:30:00       08:30:00
6     2           11:30:00       13:00:00
7	
8DROP TABLE IF EXISTS schedule;
9
10CREATE TABLE schedule
11(room        INT NOT NULL
12,schedule_start      TIME NOT NULL
13,schedule_end TIME NOT NULL
14,PRIMARY KEY(room,schedule_start)
15);
16
17INSERT INTO schedule VALUES
18(1,'09:00:00','10:00:00'),
19(1,'11:00:00','12:00:00'),
20(2,'07:30:00','08:30:00'),
21(2,'11:30:00','13:00:00'),
22(3,'09:30:00','10:30:00'),
23(3,'11:00:00','12:00:00'),
24(4,'10:30:00','10:45:00');
25
26SET @start:= '10:00:00';
27SET @end:= '11:00:00';
28
29SELECT DISTINCT x.room
30        -- or whatever columns you want from whichever table you want 
31           FROM schedule x 
32           LEFT 
33           JOIN schedule y 
34             ON y.room = x.room 
35            AND y.schedule_start < @end 
36            AND y.schedule_end > @start 
37        -- other tables can join in here
38          WHERE y.room IS NULL;
39+------+
40| room |
41+------+
42|    1 |
43|    2 |
44+------+
45	
46SELECT DISTINCT room
47           FROM schedule
48          WHERE @end <= schedule_start
49             OR @start >= schedule_end;
50+------+
51| room |
52+------+
53|    1 |
54|    2 |
55|    3 |
56+------+
57	
58SET @start = '10:00:01';
59SET @end = '10:59:59';
60
61SELECT *
62FROM `schedule` -- you probably want to select from rooms here...
63WHERE room NOT IN (
64  SELECT room
65  FROM `schedule`
66  WHERE sched_start BETWEEN @start AND @end
67  OR sched_end BETWEEN @start AND @end
68  OR @start BETWEEN sched_start AND sched_end
69  OR @end BETWEEN sched_start AND sched_end
70);
71	
72SET @start:= '10:00:00';
73SET @end:= '11:00:00';
74
75SELECT DISTINCT room
76           FROM schedule
77          WHERE room NOT IN ( SELECT room
78                                FROM schedule
79                               WHERE schedule_start < @end
80                                 AND schedule_end > @start );
81	
82SELECT DISTINCT 
83  room
84FROM schedule
85WHERE 
86    '11:00:00' <= sched_start
87    OR
88    '10:00:00' >= sched_end
89	
90SELECT *
91FROM schedule
92WHERE sched_end BETWEEN '10:00:00' AND '11:00:00'