title: Select available rooms in given date range
body: <p>I am building a hotel booking system and I just got stuck when trying to make a query that retrieves all available room types in a given range.</p>
  
  <p>I have two tables <code>Rooms</code> and <code>Reservations</code>. The <code>Rooms</code> table hold the rooms in the hotel. Their number (<code>id</code>) and their type (<code>type</code>). </p>
  
  <p>Table <code>Reservations</code> holds the reservations made by the clients. The reservation number (<code>id</code>), the associated room number (<code>room_id</code>), and date range (<code>from</code> and <code>to</code>)</p>
  
  <p>I have tried this query:</p>
  
  <pre><code>SELECT room_id as available_room_number, type
  
  FROM roomstesting
  
  LEFT JOIN reservations ON roomstesting.id = reservations.room_id
  WHERE reservations.id 
    NOT IN (reservations.e_from &lt;='"2014-03-07 19:00:00' 
             AND reservations.e_to &gt;='2014-03-08 19:00:00')
  </code></pre>
  
  <p>I was trying to get all the available room types in the range from March 7 to March 8. I was expecting to get the <code>modern room</code> as result of the query. Because <code>modern room id 4</code> has no reservations made overlapping with the date range and all the other 3 rooms all have reservations made from March 6 to March 9. But I am not getting the result I want. Below is the structure of my database (simplified)</p>
  
  <p>Rooms</p>
  
  <pre><code>| id | type         |
  |||||||||||||||||||||
  |  1 | master suite |
  |  2 | master suite |
  |  3 | modern room  |
  |  4 | modern room  |
  </code></pre>
  
  <p>Reservations</p>
  
  <pre><code>| id | room_id | from                | to                  |
  ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
  |  1 |    1    | 2014-03-05 08:00:00 | 2014-03-09 08:00:00 |
  |  2 |    2    | 2014-03-05 08:00:00 | 2014-03-09 08:00:00 |
  |  3 |    3    | 2014-03-05 08:00:00 | 2014-03-09 08:00:00 |
  </code></pre>
  
  <p>Expected result</p>
  
  <pre><code>| available_room_number | type       |
  ||||||||||||||||||||||||||||||||||||||
  |          4            | modern room|
  </code></pre>
  
  <p>If anyone here can tell me how I should approach this that would be perfect. Looking forward to your replies.</p>