How to write sql query to check room availability

The user table contains the dates between which the room in the room table should be checked for availability, the room table contains dates between which the room is booked.

image here

code

SELECT room
from room r, user u
where u.indate not between r.checkin and r.checkout and
     u.outdate not between r.checkin and r.checkout union
select room from room r, user u
where u.indate=r.checkout

If the room table lists date ranges when a given room, defined by the id column is occupied and you are trying to determine for each user, defined by the id column in the user table which rooms would be available, then you can't in principle from these two tables; there is a piece missing. As been pointed out, the so-called room table is really a reservation table and does not by necessity enumerate all the rooms in the hotel. That is, there could be a room in the hotel that currently has or has never had a reservation and has no rows in the rooms table. Presumably those rooms would be available to all users. But if we proceed on the assumption that currently every room in the hotel is represented by at least one row in the rooms table, we may proceed. But in the end what we still have is either a question poorly posed or a poor database design.

The logic is: A room.id is available to a user.id if there does not exist a room.id such that:

`user.outdate` >= `room.checkin` and `user.indate` <= `room.checkout`

The SQL:

select distinct u.id, ifnull(r.id, '') as room_id from
user u left join room r on r.id not in (
   select room.id from room where u.outdate >= room.checkin and u.indate <= room.checkout
);

See Db Fiddle

Or if you want to combine all the rooms into one column:

select id, group_concat(room_id) from (
    select distinct u.id, ifnull(r.id, '') as room_id from
	user u left join room r on r.id not in (
   	    select room.id from room where u.outdate >= room.checkin and u.indate <= room.checkout
    )
) sq
group by id

DB Fiddle

If you want rooms available you need a "room"s table with one row per rooms. Otherwise, you will never find rooms that have no reservations. What you are calling room_table is really reservations.

Then you can use not exists:

select u.*, r.room_id
from users u cross join
     rooms r
where not exists (select 1
                  from reservations re
                  where re.room_id = r.room_id and
                        re.checkin < u.outdate and
                        re.checkout > u.indate
                 );

The overlap logic is simple. A room is available if there are no reservations that overlaps with the period of time.