title: Timeslot availability multiple days mysql
body: <p>I have a question regarding free time slot retrieval in MySQL.I have 2 tables.</p>
  
  <pre><code> Table 1:
   TimeSlots (Defines the available timeslots in 15 minutes)
  
   Columns : StartTime (**Time**), EndTime(**Time**)
   Data:
  
   00:00:00 , 00:15:00
   00:15:00 , 00:30:00
   And so on until 23:45:00 , 00:00:00
  </code></pre>
  
  <p>Table 2 Scheduler (Holds the scheduled tasks)</p>
  
  <pre><code> Columns : idScheduler (**pk**) , Room (**int, fk**), StartDateTime (**DateTime**), EndDateTime (**DateTime**)
  </code></pre>
  
  <p>The query I use in order to retrieve the open timeslots for a day is:</p>
  
  <pre><code>     SELECT StartTime, EndTime FROM TimeSlots WHERE 
       NOT Exists (                
              SELECT Room, StartDateTime, EndDateTime FROM Scheduler
          WHERE Room = 'Room1'
          AND DATE(StartDateTime) =  '2012-12-20'
          AND 
              (StartDateTime &gt;= concat('2012-12-20', ' ' ,StartTime) AND
              StartDateTime &lt;= concat('2012-12-20', ' ' ,EndTime))
              OR
              (EndDateTime &gt;= concat('2012-12-20', ' ' ,StartTime) AND
              EndDateTime &lt;= concat('2012-12-20', ' ' ,EndTime))
              OR
              (EndDateTime &lt;= concat('2012-12-20', ' ' ,StartTime) AND
              StartDateTime &gt;= concat('2012-12-20', ' ' ,EndTime))
          )
  </code></pre>
  
  <p>Room and StartDateTime will become paramaters in the final solution.</p>
  
  <p>The problem I have that if a room is booked for example that spans multiple timeslots
  (Example : 1, 1, 2012-12-20 13:10:00, 2012-12-20 13:55:00)</p>
  
  <p>If I run the query mentioned above it will give this output:</p>
  
  <pre><code>13:00:00 - 13:15:00
  13:15:00 - 13:30:00
  13:30:00 - 13:45:00
  13:45:00 - 14:00:00
  </code></pre>
  
  <p>What am I missing here?</p>
  
  <p>Thanks in advance</p>