Sfrgttk

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.