SQL: Learning to write SQL and I can't solve this problem

Development
1

This SQL statement selects the room numbers for all occupied rooms at a hotel and works well. However i wish to show all the absent rooms instead.
As I am sure you know I can't just "NOT" the statement as it would select the wrong date. If anyone has a solution I would appreciate an explanation

NOTE: Date stored in the format 2016-11-25 I convert it for comparison purposes.

SELECT room_no
FROM booking
WHERE (booking_date + 0) < ('20161125') AND (booking_date + nights - 1) > ('20161125')
ORDER BY room_no

2

I believe you want something like this:

SELECT room_no
FROM booking
NOT IN
(
SELECT room_no
FROM booking
WHERE (booking_date + 0) < ('20161125') AND (booking_date + nights - 1) > ('20161125')
)
ORDER BY room_no
3

It didn't work but i see where you are going with that and it makes seance.

I will peruse this

4

Couldn't fine what was wrong with this statement but thank you.

This is proving rather tricky

5

you just have to select all where the booking has expired

SELECT room_no
FROM booking
WHERE (booking_date + nights - 1) <= ('20161125')
ORDER BY room_no

1 Like
6

This doesn't work unfortunately. I tried this, it picks up all the old booking's in the databases history will output.0

BUT .... If i group them, one mo

7

On second thought, it might not work because the query is referencing the 'booking' table which, by deduction, might not contain all available rooms of the hotel, only the ones that are/were booked. You probably should need a table room with a record for each room of the hotel or record the maximum number of room in the hotel. I'm a mistaken ?

8

no it only contains the current occupied rooms

9

Yes, assuming there are no records on the that table do you know how many rooms are available in the hotel using the info on another table on the db ?

10

I have just found a diagram hidden away. As it turns out there is although it is just called id, which is why I could not find it

Joined it below

SELECT room_no, id
FROM booking JOIN room ON (booking.room_no=room.id)
WHERE (booking_date + nights - 1) <= ('20161125')
ORDER BY room_no

I did not know that the room id field contained the room numbers

1 Like
11

I also noticed an error on my previous answer.

So it should be this, (I think):

SELECT id
FROM room
WHERE id NOT IN
(
SELECT room_no
FROM booking
WHERE (booking_date + 0) < ('20161125') AND (booking_date + nights - 1) > ('20161125')
)
ORDER BY id

1 Like
12

That is perfect thank you!

I see what you have done I can't believe I didn't think about it in this way before. Thanks I will experiment with this further.