title: Select all available items in a specific period
body: <p>So I have 2 tables caring and client, like this</p>
  
  <pre><code>client {
    id,
    name
  }
  
  caring {
    id,
    startDate,
    endDate,
    clientId
  }
  </code></pre>
  
  <p>I need to get all clients that have at least one day available between two provided dates, you can see my screenshot as reference.</p>
  
  <p><a href="https://i.stack.imgur.com/7becP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7becP.png" alt="Screenshot"></a></p>
  
  <p>In screenshot I have two clients, and I need to return both of them. As you can see, the first client have three free days (21.5.-23.5.) between provided period (16.5.-29.5.) and the second client have not any caring periods.</p>
  
  <p>So far i have tried something like this</p>
  
  <pre><code>SELECT * FROM client cl
  WHERE cl.id NOT IN (SELECT clientId FROM caring 
  WHERE endDate &gt;= CURDATE() AND endDate &lt;= DATE_ADD(CURDATE(), INTERVAL 14 DAY))
  </code></pre>
  
  <p>This one return only clients that don't have carings at all. That is partially what I need because this query don't cover first client from my screenshot. Then I tried query bellow.</p>
  
  <pre><code>SELECT ca.startDate, ca.endDate, cl.firstName, cl.lastName
  FROM caring ca
  LEFT JOIN client cl on cl.id = ca.clientId
  WHERE ca.startDate NOT IN (
      SELECT endDate
      FROM caring 
  ) AND ca.startDate &lt;= '2017-05-29' AND ca.endDate &gt;= '2017-05-16'
  </code></pre>
  
  <p>But im not getting desired results.</p>
  
  <p>Any idea how I can achieve this, thx in advance!</p>