title: SQL Scheduling - Select All Rooms Available for Given Date Range
body: <p>I'm using Microsoft's idea for storing resource and booking information. In short, resources, such as a hotel room, do not have date records and booking records have a BeginDate and EndDate. </p>
  
  <p>I'm trying to retrieve room availability information using MS's queries but something tells me that MS's queries leave much to be desired. Here's the MS article I'm referring to: <a href="http://support.microsoft.com/kb/245074" rel="nofollow">http://support.microsoft.com/kb/245074</a></p>
  
  <p>How can I retrieve available rooms for a given date range? Here's my query that returns a simple list of bookings:</p>
  
  <pre><code>SELECT r.RoomID, b.BeginDate, b.EndDate
  FROM tblRoom as r INNER JOIN tblBooking b ON r.RoomID = b.AssignedRoomID;
  </code></pre>
  
  <p>But I'm still baffled as to how I can get a list of available rooms for a given date range?</p>
  
  <p>I am using Microsoft Access but I'd like to keep my queries DBMS agnostic, as much as possible. While this isn't really my question, if you feel that the data model I'm using is unsound, please say so, as I am willing to consider a better way of storing my data.</p>
  
  <p><strong>Edit1:</strong><br>
  I failed to mention that I don't like MS's queries for two reasons. First of all, I'm really confused about the 3 different OR operators in the WHERE clause. Are those really necessary? Secondly, I don't like the idea of saving a query and using it as a table although I'm willing to do that if it gets the job done, which in this case I believe it does.</p>
  
  <p><strong>Edit2:</strong><br>
  This is the solution I've landed on using the excellent answer given here. This is MS Access SQL dialect (forgive me):  </p>
  
  <pre><code>SELECT * FROM tblRoom AS r  
  WHERE RoomID NOT IN  
   (SELECT AssignedRoomID as RoomID From tblBooking  
    WHERE assignedroomid IS NOT NULL AND assignedroomid = r.roomid AND 
      (BeginDate &lt; @BookingInquiryEndDate AND EndDate &gt; @BookingInquiryBeginDate)  
   )
  </code></pre>