SQL query. Should I loop through specific date range? [closed]

I am making a hotel management system. I need your help with appropriate statement to execute. Can you please have a look?

This is all room_types = 3, their check-in and check-out date

If I make query “SELECT * from rooms WHERE room_type = 3 AND check_in_date BETWEEN ‘2021-02-12’ AND ‘2021-02-13′” this is what it returns

it returns only one room

but BETWEEN ‘2021-02-12’ AND ‘2021-02-13′” there will 5 rooms with room_type = 3 in house. How can write a query that returns it?

I need to return these

Because all those 4 rooms with type=3 will be in house BETWEEN ‘2021-02-12’ AND ‘2021-02-13’

I am using MariaDB with ORACLE syntax.

Thank you!

Answer

I suspect that you want a date range overlap:

select * 
from rooms 
where room_type = 3 
  and check_in_date  <= '2021-02-13'
  and check_out_date >= '2021-02-12'

This brings rooms that have a reservation that overlap the given range.