title: Using MySQL query in selecting room availability
body: <p>I had this database structure in a hotel reservation in terms of room availability</p>
  
  <p>below is my table of <code>room</code></p>
  
  <pre><code>room_num    roomtype     
  101         Single    
  102         Single    
  103         Single    
  111         Deluxe    
  112         Deluxe   
  113         Deluxe    
  114         Deluxe    
  115         Deluxe    
  116         Deluxe    
  121         Superior  
  122         Superior  
  </code></pre>
  
  <p>and the table below is <code>room_booked</code> which is records booked room numbers</p>
  
  <p><code>dor</code> is checkin date and <code>dco</code> is date checkout</p>
  
  <pre><code>room_num        dor         dco
    111        2014-06-01  2014-06-06
    112        2014-06-01  2014-06-06
    113        2014-06-01  2014-06-06
    114        2014-06-01  2014-06-06
    115        2014-06-01  2014-06-06
    116        2014-06-01  2014-06-06
    112        2014-05-18  2014-05-21
    113        2014-08-01  2014-08-04
  </code></pre>
  
  <p>In this case, all of Deluxe room are booked from <code>2014-06-01</code> to <code>2014-06-06</code>. Then I selected Deluxe room available from 2014-06-01 to 2014-06-06 by using this MySQL code below</p>
  
  <pre><code>SELECT
      room.room_num
  FROM
      room
  LEFT JOIN
      room_booked ON room_booked.room_num = room.room_num
  WHERE
      (
          (
              dor IS NULL AND dco IS NULL
          )
          OR (
              -- wished booking date is before DOR
              '2014-06-06' &lt; dor
              -- OR wished booking date is after DCO
              OR '2014-06-01' &gt;=  dco
          )
      )
      -- room type
      AND roomtype = 'Deluxe'
  </code></pre>
  
  <p>However it shows Deluxe room no. 112 and 113 which is booked before and after selected date range above instead of 0 or fully booked. Is there any solution to fix this code?</p>