title: Sql search date between range of dates
body: <p>I have two tables in database</p>
  
  <ol>
  <li><p>rooms: contains details of rooms</p></li>
  <li><p>booking: containd booking information about rooms  </p></li>
  </ol>
  
  <p>rooms:</p>
  
  <pre><code>+----+----------+---------------+---------+
  | id | hotel_id | room_category | room_no |
  +----+----------+---------------+---------+
  |  1 |        1 | delux         |       1 |
  |  2 |        1 | delux         |       2 |
  |  3 |        1 | delux         |       3 |
  |  4 |        1 | delux         |       4 |
  |  5 |        1 | delux         |       5 |
  +----+----------+---------------+---------+
  </code></pre>
  
  <p>bookings:</p>
  
  <pre><code>+----+------------+----------+---------------+--------------+---------------+---------+----------------+
  | id | booking_id | hotel_id | room_category | checkin_date | checkout_date | room_no | booking_status |
  +----+------------+----------+---------------+--------------+---------------+---------+----------------+
  |  1 |          1 |        1 | delux         | 2016-08-25   | 2016-08-30    |       1 | y              |
  |  2 |          2 |        1 | delux         | 2016-08-25   | 2016-08-28    |       2 | y              |
  +----+------------+----------+---------------+--------------+---------------+---------+----------------+
  </code></pre>
  
  <p>Now i write an query:</p>
  
  <pre><code>select * from rooms
   where
   hotel_id =1 and
        room_category="delux" 
         and id not in(
  
  select room_no
  from bookings 
  where 
         checkin_date between '2016-08-25' and '2016-08-28'
         or
         checkout_date between '2016-08-25' and '2016-08-28'
  
         );
  </code></pre>
  
  <p>problem:
  1. while searching for availability from 26-27 result contains room no  1and 2 as well</p>