SELECT COUNT(free rooms categories in complex query)

Question

I'm developing a hotel room booking system.

This system will contain some quantity of hotels, rooms & room_categories. I have tables for these things already.

At the current moment I need to build a query to get the quantity of available rooms for each of room category on given dates.

My rooms table is like this:

--------------------------------------------
| id |  name   | hotel_id |room_category_id|
--------------------------------------------
|  1 | Room #1 |    1     |     1          |
|  2 | Room #2 |    1     |     1          |
|  3 | Room #3 |    1     |     2          |
|  4 | Room #4 |    1     |     2          |
|  5 | Room #5 |    1     |     3          |
|  6 | Room #6 |    1     |     3          |
|  7 | Room #7 |    1     |     4          |
|  8 | Room #8 |    1     |     4          |
--------------------------------------------

Room categories table is like this:

----------------------------------
| id |   name   | price | volume |
----------------------------------
|  1 | Standart |  $100 |   2    |
|  2 | Comfort  |  $150 |   2    |
|  3 | Half Lux |  $200 |   3    |
|  4 | Lux      |  $250 |   3    |
----------------------------------

Bookings table is like this:

------------------------------------------------------------------------
| id | booking_start | booking_end | room_id |room_category_id|hotel_id|
------------------------------------------------------------------------
|  1 |  2019-06-17   | 2019-07-17  |    1    |       1        |    1   |
|  2 |  2019-06-17   | 2019-07-17  |  null   |       2        |    1   |
|  3 |  2019-06-17   | 2019-07-17  |  null   |       3        |    1   |
------------------------------------------------------------------------

I'm trying this query

SELECT room_categories.name, COUNT(room_categories.name) as quantity FROM rooms

INNER JOIN room_categories
ON rooms.room_category_id = room_categories.id

WHERE hotel_id=1
AND room_categories.id NOT IN  (
Select bookings.room_category_id FROM bookings 
  WHERE '2019-07-28' between booking_start and booking_end 
      OR booking_end between  '2019-06-17'  and  '2019-07-28'  
      OR  '2019-06-17' between booking_start and booking_end 
      OR booking_start between '2019-06-17'  and '2019-07-28'
)

GROUP BY room_categories.name
ORDER BY quantity

Let's imagine I have 2 rooms for each category and 1 booking for each room category. This query return ONLY category I don't have ANY bookings on (in my case room_category=4).

-------------------
|  name  |quantity|
-------------------
|Standart|    2   |
-------------------

How should I build a query to get correct counts here like this:

|room_category|count|
---------------------
| Standart    |  1  |
| Comfort     |  1  |
| Half Lux    |  1  |
| Lux         |  2  |
---------------------

Answer 1

Your question is a little vague on what you mean by "available" and what dates you want. Let me assume that you want the numbers of rooms, by category, that are available for the entire period from 2019-06-17 to 2019-07-28 (that seems like a long time to me and a hotel that has rooms for that entire period does not seem to have a very good business).

SELECT rc.name,
       COUNT(b.room_id IS NULL) as quantity 
FROM rooms r JOIN
     room_categories rc
     ON rc.room_category_id = r.id LEFT JOIN
     bookings b
     ON b.room_id = r.room_id AND
        b.booking_start <= '2019-07-28' AND
        b.booking_end >= '2019-06-17'
WHERE r.hotel_id = 1
GROUP BY rc.name
ORDER BY quantity DESC;

The LEFT JOIN is matching any booking that has a booking during the date range. The outer query is then counting rows that do not match. Note that the filter is not in the WHERE clause, so you can get counts of 0.