Re: Hotel Booking dates subquery

From: Damjan S. Vujnovic <damjan_at_galeb.etf.bg.ac.yu>
Date: Sat, 9 Nov 2002 18:54:40 +0100
Message-ID: <aqji3h$ipe$1@news.etf.bg.ac.yu>

I'm not quite sure that I understand your design (the parenthesis in your query are not ballanced and, as far as I understand your model, room_status cannot be both reserved and checked_in), but if your tables look somewhat like:

CREATE TABLE rooms (

       room_id INTEGER NOT NULL PRIMARY KEY, );

CREATE TABLE bookings (

       booking_id INTEGER NOT NULL PRIMARY KEY,
       date DATETIME,
       room_status ???,
       room_id INTEGER NOT NULL REFERENCES rooms(room_id),

);

and your query looks like:

SELECT room_id
FROM rooms
WHERE room_id NOT IN (

    SELECT room_id
    FROM bookings
    WHERE (room_status IN (reserved, checked_in)) AND (date BETWEEN start AND finish))

than, it's incorrect. Let's say that you want to find empty rooms between Feb-1.-2002. and Mar-1.-2002. and let's say that the room XYZ was checked-in on Jan-1.-2002. and checked-out on Apr-1.2002. Your query will say that it was free, but it obviously wasn't.

Tip: I think your design is bad and I suggest you to re-design your tables
(if possible, of course) in such a way that will simplify your query...

Best wishes,
Damjan S. Vujnovic

University of Belgrade
School of Electrical Engineering
Department of Computer Engineering & Informatics Belgrade, Yugoslavia

http://galeb.etf.bg.ac.yu/~damjan/

---

"Scotty" <invallid_at_invalid.spam> wrote:
Im looking for a way to find empty rooms hotel between a certain date.

The room has four possible states; reserved, checked in, checked out, cancelled.

Now i'm not sure of the correct syntax but are the mechanics correct?

table with rooms id's -> Rooms
table with bookings -> Bookings

Select room_id
from rooms
where room_id !=
((select room_id

from booking
where room_status= reservation
and room_status= checked_in
and room_id =)
(select room_id

from booking
where date between start and finish))

Does that look about right? Received on Sat Nov 09 2002 - 11:54:40 CST