Re: Hotel Booking dates subquery

First of all, your query should return only room_id's, so I'll assume that your query looks somewhat like (and I'll reference it as 'your query' in this post):

select room_id
from booking
where
to_date('10-11-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy')< to_date(startdate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy') and
to_date('20-11-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy')< to_date(startdate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy') or
to_date('10-11-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy')> to_date(enddate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy') and
to_date('20-11-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy')> to_date(enddate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy');

Second, AFAI understand, you are looking for free rooms between two given dates. Let's suppose that the table BOOKING is empty (no bookings at all). For any two given dates, your query will say that there were no free rooms (but all rooms were free).

Third, try the following (the only difference to your example is when inserting touple booking_id = 4, you set room_id=1):

insert into room(room_id) values (1);
insert into room(room_id) values (2);
insert into room(room_id) values (3);
insert into room(room_id) values (4);
insert into room(room_id) values (5);
insert into room(room_id) values (6);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (1,1,to_date('01-11-02','DD-MM-YY'
),to_date('05-11-02','DD-MM-YY'),100);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (2,2,to_date('01-11-02','DD-MM-YY'
),to_date('17-11-03','DD-MM-YY'),100);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (3,3,to_date('01-11-02','DD-MM-YY'
),to_date('30-11-02','DD-MM-YY'),100);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (4,1,to_date('13-11-02','DD-MM-YY'
),to_date('17-11-02','DD-MM-YY'),100);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (5,5,to_date('13-11-02','DD-MM-YY'
),to_date('30-11-02','DD-MM-YY'),100);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (6,6,to_date('25-11-02','DD-MM-YY'
),to_date('30-11-02','DD-MM-YY'),100);

/*

   ROOM_ID

         1
         2
         3
         4
         5
         6

BOOKING_ID    ROOM_ID STARTDATE ENDDATE     DURATION
---------- ---------- --------- --------- ----------
         1          1 01-NOV-02 05-NOV-02        100
         2          2 01-NOV-02 17-NOV-03        100
         3          3 01-NOV-02 30-NOV-02        100
         4          1 13-NOV-02 17-NOV-02        100
         5          5 13-NOV-02 30-NOV-02        100
         6          6 25-NOV-02 30-NOV-02        100

*/

select room_id
from booking
where
to_date('10-11-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy')< to_date(startdate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy') and
to_date('20-11-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy')< to_date(startdate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy') or
to_date('10-11-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy')> to_date(enddate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy') and
to_date('20-11-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy')> to_date(enddate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy');

Your query will say (again) that rooms 1 and 6 were free, but room 1 wasn't it was occupied between 13-NOV-02 and 17-NOV-02! Received on Tue Nov 12 2002 - 02:19:54 CET