title: MySQL select rows where date not between date
body: <p>I have a booking system in which I need to select any available room from the database. The basic setup is:</p>
  
  <pre><code>table: room
  columns: id, maxGuests
  
  table: roombooking
  columns: id, startDate, endDate
  
  table: roombooking_room:
  columns: id, room_id, roombooking_id
  </code></pre>
  
  <p>I need to select rooms that can fit the requested guests in, or select two (or more) rooms to fit the guests in (as defined by maxGuests, obviously using the lowest/closet maxGuests first)</p>
  
  <p>I could loop through my date range and use this sql:</p>
  
  <pre><code>SELECT `id`
  FROM `room` 
  WHERE `id` NOT IN
  (
      SELECT `roombooking_room`.`room_id`
      FROM `roombooking_room`, `roombooking`
      WHERE `roombooking`.`confirmed` =1
      AND DATE(%s) BETWEEN `roombooking`.`startDate` AND `roombooking`.`endDate`
  )
  AND `room`.`maxGuests`&gt;=%d
  </code></pre>
  
  <p>Where %$1 is the looped date and %2d is the number of guests to be booked in. But this will just return false if there are more guests than any room can take, and there must be a quicker way of doing this rather than looping with php and running the query?</p>
  
  <p>This is similar to part of the sql I was thinking of: <a href="https://stackoverflow.com/questions/271595/getting-dates-between-a-range-of-dates">Getting Dates between a range of dates</a> but with Mysql</p>
  
  <hr>
  
  <p>Solution, based on ircmaxwell's answer:</p>
  
  <pre><code>$query = sprintf(
          "SELECT `id`, `maxGuests`
          FROM `room`
          WHERE `id` NOT IN
          (
              SELECT `roombooking_room`.`room_id`
              FROM `roombooking_room`
              JOIN `roombooking` ON `roombooking_room`.`roombooking_id` = `roombooking`.`id`
              WHERE `roombooking`.`confirmed` =1
              AND (`roomBooking`.`startDate` &gt; DATE(%s) OR `roomBooking`.`endDate` &lt; DATE(%s))
          )
          AND `maxGuests` &lt;= %d ORDER BY `maxGuests` DESC",
          $endDate-&gt;toString('yyyy-MM-dd'), $startDate-&gt;toString('yyyy-MM-dd'), $noGuests);
          $result = $db-&gt;query($query);
          $result = $result-&gt;fetchAll();
  
          $rooms = array();
          $guests = 0;
          foreach($result as $res) {
              if($guests &gt;= $noGuests) break;
              $guests += (int)$res['maxGuests'];
              $rooms[] = $res['id'];
          }
  </code></pre>