title: SQL for hotel room availability in ASP c#
body: <p>I have these three tables:
  <a href="https://i.stack.imgur.com/4r8k4.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4r8k4.png" alt="enter image description here"></a></p>
  
  <p>With this SELECT statement:</p>
  
  <pre><code>SELECT DISTINCT r.roomId,rt.roomTypeName,rt.roomTypeDesc,rt.roomTypePrice
  FROM Room r left join RoomType rt 
      on r.roomTypeId=rt.roomTypeId 
  WHERE roomStatus='Online' 
       AND NOT EXISTS(
           SELECT 1 FROM Reservation b WHERE b.roomId = r.roomId 
             AND ('7/11/2015' BETWEEN b.checkInDate
             AND b.checkOutDate OR '8/11/2015' BETWEEN b.checkInDate 
             AND b.checkOutDate OR ('7/11/2015'&lt;= b.checkInDate AND '8/11/2015' &gt;= b.checkOutDate)))
  order by rt.roomTypePrice
  </code></pre>
  
  <p>I get this result</p>
  
  <p><a href="https://i.stack.imgur.com/gKRoF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gKRoF.png" alt="enter image description here"></a></p>
  
  <p>How can I count the number of available rooms of each room type, so that I can use a drop down list in [RESULT] to allow user select the quantity that they want? (Similiar as the website Agoda.com.)</p>