SQL guest house - finding an empty room on a given date

Question

I'm attempting to learn SQL and currently doing the guest house questions found on sqlzoo. I'm stuck on Question 13 and can't seem to figure it out how to fix it.

The question is: Free rooms? List the rooms that are free on the day 25th Nov 2016.

And below is my attempted solution. It returns the empty rooms as well as rooms that were previously booked but not checked out yet.

SELECT id
from room 
WHERE id NOT IN(
SELECT room_no
FROM booking 
AND occupants=0
AND '2016-11-25' NOT IN (DATE_ADD(booking_date,INTERVAL nights DAY))

Database design

Link to the SQL zoo question

Any help appreciated.

Answer 1

use not exists

select room_no,booking_date,nights from booking b1
where not exists ( select 1 from booking b2 
                 where b2.booking_id=b1.booking_id
                  and b2.room_no=b1.room_no and
                 (b2.booking_date<='2016-11-25'
                 and DATE_ADD(b2.booking_date, INTERVAL nights  DAY)>='2016-11-25' ) 
                  )

Answer 2

You can alias a query to get the bookings for that date and then right join on rooms. Any NULLs in the bookings are open rooms.

SELECT id 
FROM
 (Select * FROM booking
  WHERE '2016-11-25' >= booking_date
   AND '2016-11-25' <= DATEADD(DAY, nights-1,booking_date)
  ) AS a
RIGHT JOIN room ON (a.room_no=room.id)
  WHERE booking_date IS null