Given this schema:

```
Hotel(hotelNo, hotelName, city)                      #hotelNo is PK
Room (roomNo, hotelNo, type, price)                  #(roomNo, hotelNo) is PK
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) #(hotelNo,guestNo, dateFrom) is PK
Guest(guestNo, GuestName, GuestAddress)              #guestNo is PK

```

**Q:** **What is the lost income from unoccupied rooms at each hotel today?**

Looking at the solution I ended up with a very different answer (more complicated but basically I found `all rooms in hotels` and computed the `Difference` set operator against `all rooms which are booked today` and then calculated the price for the remaining rooms with a group by clause - I'm not even sure it would work, but on paper it looks valid). However, looking at the example solution:

```
SELECT hotelNo, SUM(price) FROM Room r
WHERE roomNo NOT IN
    (SELECT roomNo FROM Booking b, Hotel h
    WHERE (dateFrom <= CURRENT_DATE AND
        dateTo >= CURRENT_DATE) AND
        b.hotelNo = h.hotelNo)
GROUP BY hotelNo; 

```

Why do we need to join the Booking and Hotel. I see we join the two relations based on `b.hotelNo=h.hotelNo` but looking at the schema, the Booking provides details on the RoomNo (which is booked) and provides information on which Hotel and the dates... Surely that's all the information we need to work with a Group By clause and achieve our desired result? Or am I mistaken?