Re: Hotel Booking dates subquery

Damjan S. Vujnovic wrote:

>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...

I can't think of anyway to do it other than the normalised method.

Basically all I have to do is find whether a start date or a finish fall between a start and finish date, isnt it?

It cant be that difficult! *pulls hair out*

Actually I'm starting to read up on views, could be another blind alley, but what the heck... Received on Sun Nov 10 2002 - 00:10:48 CET