SQL Find specific date where rooms are not fully taken

Question

I have two tables:

One that has a table like this: Table Name Reservations:

Name Room Seats Date
John 101  20    11/22

---

Table Name Rooms:

Name Seats
101  30
202  40

SELECT *
FROM Reservations
WHERE date = '2020-11-22' AND
SELECT Reservations.Room
FROM Reservations
WHERE Reservations.Room NOT IN(SELECT Room.Name FROM Room)

I tried the statement above, but it is not working. I want the command to return all the rooms in a date that are not used. It should return Room 202. That is it.

Answer 1

I understand that you want rooms that have seats left for the given date. One option uses a subquery for filtering:

select ro.*
from rooms ro
where ro.seats > (
    select coalesce(sum(re.seats), 0)
    from reservations re
    where re.room = ro.name and re.date = '2020-11-12'
)

On the other hand, if you want rooms for which no reservation at all exists for the given date, then use not exists:

select ro.*
from rooms ro
where not exists (
    select 1
    from reservations re
    where re.room = ro.name and re.date = '2020-11-12'
)