Return available rooms depending 2 others tables Here 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 |
|--------|---------------|-------------|-----------|-------------------|---------------|


___Availabilities

|--------|------------|------------|------------|
| 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's a quick description of these 3 tables:


_Rooms contains informations about the rooms I can rent in my hotel.
___Rates contains the rate informations I can apply for a special period and for rooms (if RAT_RoomsAffected is set to all it means that the rate can be applied for all the rooms I have in my hotel. If RAT_RoomsAffected is set to list, the rate could be applied only the the rooms id in the RAT_RoomsList field).
___Availabilities list per day the possibility (Open) to book this room or the no possibility to book a room (Close).




My problem is the following:

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


This rooms should be with the Open status (AVA_Status) for the dates a client wants to book in my hotel. For example, from the 2019-04-02 to the 2019-04-04 (2 nights).
This rooms should be linked with a rate in _Rates table. So depending the client dates of stay I need to loop into this table and find a rate for this period.
Finally the rates found should match the ROO_Id found in the first step. A ROO_Id could match if RAT_RoomsAffected = all or when this ROO_Id is found into the RAT_RoomsList list.




The desired output should be this one from the 2019-04-02 to the 2019-04-04:

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




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

Thanks so much for any help I will receive.
SELECT ro.ROO_Id, ro.ROO_Name, ra.RAT_Price
FROM ___Rooms ro, ___Rates ra, ___Availabilities av
WHERE (FIND_IN_SET(ro.roo_id, ra.RAT_RoomsList) OR ra.RAT_RoomsList='')
  AND av.AVA_Status = 'Open'
  AND av.AVA_RoomId = ro.roo_id
  AND av.AVA_Date BETWEEN GREATEST(ra.RAT_DateStart, '2019-04-02') 
                      AND LEAST(ra.RAT_DateEnd, '2019-04-04')
GROUP BY ro.ROO_Id, ro.ROO_Name, ra.RAT_Price
HAVING COUNT(*) = 1 + DATEDIFF('2019-04-04', '2019-04-02')


fiddle