title: Retrieving available rooms and their beds for a given date range from database
body: <p>I have read carefuly through all the posts on here about this topic, but my question has one extra catch, where I need to get available beds of each room because its a hostel and some rooms are shared.</p>
  
  <p>Now I have tried doing this in <a href="https://stackoverflow.com/questions/35516778/compare-two-multidimensional-arrays-and-unset-matched-elements">PHP</a>, but then realized I haven't even taken in account the date range. So now I am thinking since I already have a query that retrieves all reservations occurring within a given date range and then compare the retrieved rooms and their beds with the room table and show only rooms and their beds that are not occupied. But I can't figure out how to work around the beds because they are not an entity, only a count of total beds in each room. But then a reservation says for which bed in the room that reservation is made..</p>
  
  <p>Here are my tables</p>
  
  <p><strong>Rooms</strong></p>
  
  <p><a href="https://i.stack.imgur.com/AI5Gk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/AI5Gk.png" alt="enter image description here"></a></p>
  
  <p><strong>Reservations</strong></p>
  
  <p><a href="https://i.stack.imgur.com/iGYhj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iGYhj.png" alt="enter image description here"></a></p>
  
  <p>Now the query that I use to retrieve all reserved rooms and their beds is</p>
  
  <pre><code>SELECT rooms_id, bed 
  FROM reservations 
  WHERE `to` &gt; '2016-02-18' AND `from` &lt; '2016-02-24'
  </code></pre>
  
  <p>The first date if my input variable $from and the second date is input variable $to and it allows to retrieve not only rooms that have a reservation that starts within $from and $to but also all reservations that started before the date range and end inside, started inside and end after the date range and finally reservations that started before and end after the date range. So this exact query above would return the following table</p>
  
  <p><a href="https://i.stack.imgur.com/VcdrZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VcdrZ.png" alt="enter image description here"></a></p>
  
  <p>which I can then visualize in my app like this
  <a href="https://i.stack.imgur.com/Fd37M.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Fd37M.png" alt="enter image description here"></a></p>
  
  <p>But this is where I get stuck. I have no idea how to match the data I have in order to find all available rooms BUT ALSO THE BEDS.
  The desired table <strong>AVAILABLE ROOMS</strong> for the date range from '2016-02-18' and to '2016-02-24' should look like this:</p>
  
  <pre><code>|||||||||||||||||||||||||||||||||
  || rooms_id || bed_number ||
  ||||||||||||||||||||||||||||
  ||    1     ||    1       ||
  ----------------------------
  ||    1     ||    2       ||
  ----------------------------
  ||    2     ||    5       ||
  ----------------------------
  ||    2     ||    6       ||
  ----------------------------
  ||    2     ||    7       ||
  ----------------------------
  ||    2     ||    8       ||
  ----------------------------
  </code></pre>
  
  <p>You can see this in the picture where I show how it looks in my app. The only rooms and beds that are available between the two dates are the Luxury Room and its bed number 1 and bed number 2 and the Dorm rooms beds 5,6,7,8 because 1-4 have a reservation occurring on at least on one of the desired dates</p>
  
  <p>The only idea I had was using NOT IN, but that only works if I didn't care about the beds and also the output is of here it is</p>
  
  <pre><code>SELECT *
  FROM `rooms`
  WHERE `id` NOT IN
       (SELECT rooms_id FROM reservations WHERE `to` &gt; '2016-02-18' AND `from` &lt; '2016-02-24')
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/7hbrV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7hbrV.png" alt="enter image description here"></a></p>
  
  <p>instead of what I "sketched" above</p>
  
  <p>I'd appreciate any tips and ideas on how approach this.
  Part of me worries that this will all come down to me not treating the beds as entities and having to do that, even though as I will <strong>never be storing</strong> any sort of information on the beds such as their color, position, quality, price, etc...</p>
  
  <p><strong>REPLY TO @Paul-Spiegel</strong></p>
  
  <p>That is amazing, but is there any way to get the free beds as numbers as well instead of total number. Because then when the person makes a reservation I have to assign it to on of the beds. So if the result could be</p>
  
  <pre><code>| room_id | title       | beds_total | available_bed_nrs |
  ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
  | 1       | luxury room | 2          | 1, 2              |
  | 2       | dorm room   | 8          | 5, 6, 7, 8        |
  </code></pre>
  
  <p>instead of</p>
  
  <p><a href="https://i.stack.imgur.com/Ierw4.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Ierw4.png" alt="enter image description here"></a></p>