Pfthb

The question I'm trying to answer wants me to query the DB for:
List the rooms that are free on the day 25th Nov 2016.

This is what I have so far,

SELECT room_no FROM booking b
JOIN room ON b.room_no = room.id
WHERE booking_date = '2016-11-25' AND occupants IS NULL

I've modified it to:

SELECT room_no FROM booking b
JOIN room ON b.room_no = room.id
JOIN guest ON guest_id = guest.id
WHERE booking_date = '2016-11-25' AND guest_id IS NULL

And I'm still getting the table header with no values, when I should be getting room ids 207, 210, 304.

I'm sure I'm missing something but I just can't see what, any help is much appreciated.

https://sqlzoo.net/wiki/Guest_House_Assessment_Hard

Thanks :)

Although your SQL query is not correct (you can check if occupants IS NULL but not compare them with the = operator), you are doing an INNER JOIN, which will only return rows matching in both tables.

Rooms without a booking for that day should not have a booking associated because nobody made a booking. You have to get the rooms that do not have a booking starting before Nov 25th or that date, and ending later than that.

You can get what you want with a subquery:

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

I assumed you are using MariaDB (therefore I used DATE_ADD function) as that is the DBMS that SQLZOO appears to be based on.

You are not taking the number of nights into account, so the logic is more like this:

SELECT room_no
FROM room r LEFT JOIN
 booking b
 ON b.room_no = room.id AND
 '2016-11-25' < booking_date + interval nights day AND
 '2016-11-25' >= booking_date
WHERE b.room_no IS NULL;