mysql – Returns available rooms according to 2 other tables

Here is my table structure:

___Rooms

| -------- | ---------- |
| ROO_Id | ROO_Name |
| -------- | ---------- |
| 1 | Blue |
| 2 | Red |
| 3 | Yellow |
| -------- | ---------- |

___Rates

| -------- | --------------- | ------------- | ---------- - | ------------------- | --------------- |
| RAT_Id | RAT_DateStart | RAT_DateEnd | RAT_Price | RAT_RoomsAffected | RAT_RoomsList |
| -------- | --------------- | ------------- | ---------- - | ------------------- | --------------- |
| 90 | 2019-04-01 | 2019-05-01 | 139.00 | all | |
| 91 | 2019-05-01 | 2019-12-31 | 159.00 | list | 1,2 |
| 92 | 2019-05-01 | 2019-12-31 | 129.00 | list | 3 |
| -------- | --------------- | ------------- | ---------- - | ------------------- | --------------- |

___ Availability

| -------- | ------------ | ------------ | ------------ |
| AVA_Id | AVA_RoomId | AVA_Status | AVA_Date |
| -------- | ------------ | ------------ | ------------ |
| 203 | 1 | Open | 2019-04-01 |
| 204 | 1 | Open | 2019-04-02 |
| 205 | 1 | Open | 2019-04-03 |
| 206 | 1 | Open | 2019-04-04 |
| 207 | 1 | Open | 2019-04-05 |
| 208 | 1 | Close | 2019-04-06 |
| 209 | 1 | Close | 2019-04-07 |
| -------- | ------------ | ------------ | ------------ |

Here is a quick description of these 3 tables:

  • _Rooms contains information about rooms that I can rent in my hotel.
  • ___Rates contains information about rates that I can charge for a special period and for rooms (if RAT_RoomsAffected is set to all, it means that the rate can be applied to all rooms in my hotel.) RAT_RoomsAffected is set to list, rate can only be applied to rooms id in the RAT_RoomsList field).
  • ___ Availability list by day the possibility (Open) to reserve this room or the non possibility to reserve a room (Close).

My issue is the following:

I would like to list the rooms that meet the following two conditions:

  • This room should be with the Open status (AVA_Status) for the dates that a customer wants to book at my hotel. For example, from 2019-04-02 to 2019-04-04 (2 nights).

  • These rooms must be linked to a rate in _Rates table. So, depending on the client's dates of stay, I have to go through this table and find a rate for that period.

  • Finally, the fares found must correspond to ROO_Id found in the first step. A ROO_Id could match if RAT_RoomsAffected = all or when that ROO_Id is in the RAT_RoomsList listing.


The desired exit should be from 2019-04-02 to 2019-04-04:

| -------- | ---------- | ----------- |
| ROO_Id | ROO_Name | RAT_Price |
| -------- | ---------- | ----------- |
| 1 | Blue | 139.00 |
| -------- | ---------- | ----------- |

Here is the SQLFiddle to help you:
https://www.db-fiddle.com/f/kPY6FmKw1SZJjTHUbLj5Pe/0

Thank you very much for any help I will receive.