Can't query selected data when my date is full

Question

I have a database structure like this:

And I want to check the available room types on a specific date, so I run this query.

SELECT type.type_name, orders_details.access_date 
FROM type 
LEFT JOIN rooms ON type.type_id = rooms.room_id
LEFT JOIN orders_details ON rooms.room_id = orders_details.room_id 
                         AND orders_details.access_date BETWEEN "2023-01-19" AND "2023-01-21" 
WHERE orders_details.access_date IS NULL 
GROUP BY type.type_name

For a type of rooms, I have multiple rooms. I have 3 rooms with the same type ID. When booking one of the rooms with the same type, the type is not shown when I run the query.

How do I still query the type even if a room of that type is still available?

Answer 1

This part matching type_id with room_id seems illogical.

LEFT JOIN rooms ON type.type_id = rooms.room_id