title: query for available rooms in hotel reservation
body: <p>i was tasked to create an online hotel reservation system. the problem i encountered is the query to find the available rooms for a certain room category for a certain date range. My database design involves 4 tables. The database design is as follows:</p>
  
  <pre><code>tbl_reservationdetails ( stores the general details of the reservation )
   pk resrvdtl_id (primary key)
   fk client_id (insignificant for now)
      start_date (customer's check-in-date)
      end_date (customer's check-out-date)
  
  tbl_reservation (stores the rooms reserved for a particular reservation )
   pk reserv_id (primary key)
   fk resrvdtl_id (foreign key, to know to whom and when the room should be occupied)
   fk room_id (the room reserved)
  
  tbl_room
   pk room_id (primary key)
      room_number 
   fk room_categId (to know what category this room belongs to)
  
  tbl_roomcategory
   pk room_categId (primary key)
      room_category (description of category.. example: Suite, Superior, Deluxe etc. in my case... there are four categories)
  </code></pre>
  
  <p>user input is the dates (start and end), and the category of room he wants. I'm quite new at this... how do I query to check the available room for that category for a certain date????? </p>
  
  <p>any response to this would be highly appreciated... thanks </p>