title: SQL query to find free rooms in hotel
body: <p>I use a query to find free rooms from <code>hotel</code> DB. I wrote a query which select rooms that not in <code>booking</code> table:</p>
  
  <pre><code>SELECT * FROM room WHERE roomId NOT IN
    (SELECT roomId FROM booking b WHERE STR_TO_DATE('${endDate}', '%m-%d-%Y') &lt;= b.endDate AND
     STR_TO_DATE('${startDate}', '%m-%d-%Y') &gt;= b.startDate);
  </code></pre>
  
  <p>My <code>booking</code> table looks like:</p>
  
  <pre><code>+-----------+------------+------------+--------+---------+
  | bookingId | startDate  | endDate    | roomId | guestId |
  +-----------+------------+------------+--------+---------+
  |         1 | 2016-03-12 | 2016-03-22 |      1 |       1 |
  +-----------+------------+------------+--------+---------+
  </code></pre>
  
  <p>But if my <code>startDate</code> is <code>2016-03-10</code> and <code>endDate</code> is <code>2016-03-25</code> - I've got already booked room from <code>2016-03-12</code> to <code>2016-03-22</code>. How I can fix it? I don't need to show room that booked between my dates.</p>