title: hotel reservation system SQL query: identify when one specific room is available
body: <p>I have a query for a hotel reservation system and I need to find dates when a specific room is available. (It's a boutique hotel where people reserve specific rooms, so they know the exact room they want before they get to this code. <strong>The result I'm after is the detail for ONE room, the one I specify in the query-- I am not looking for info on multiple rooms.</strong>)</p>
  
  <p>The 'availability' table schema is simple, each row is:</p>
  
  <pre><code>room_id
  date_occupied - a single day that is occupied (like '2011-01-01')
  </code></pre>
  
  <p>So, if, for example, a room is occupied from January 1 to January 5, five rows are added to the availability table, one for each day the room is occupied.</p>
  
  <p>Here's what I'm trying to work out:</p>
  
  <ul>
  <li>the query to find when a specific room is available between a start and end date, sort of like:</li>
  </ul>
  
  <blockquote>
  <pre><code>SELECT rooms.* FROM rooms, availability
   WHERE rooms.id = 123
     AND availability.room_id = rooms.id
     AND nothing between start_date and end_date is in availability.date_occupied
  </code></pre>
  </blockquote>
  
  <ul>
  <li>I'm also seeking a similar query where I just want to see if a specific room is available for the start date and the following two days, something like:</li>
  </ul>
  
  <blockquote>
  <pre><code>SELECT rooms.* FROM rooms, availability
   WHERE rooms.id = 123
     AND availability.room_id = rooms.id
     AND start_date, start_date+1day and start_date+2days is not in availability.date_occupied
  </code></pre>
  </blockquote>
  
  <p>I'm a bit stuck trying to figure out the exact joins. Any suggestions? Note that if it helps, I'm totally open to a different schema for the availability table. Whatever makes the queries work most efficiently.</p>