This code works fine to find an available room within certain date, but it does not work to show a room that has been booked and canceled
The "hotel" has 4 rooms and 1 of them has been booked an canceled
So even if I make a cancelation, the select method keeps giving me 3 results. Maybe because the second AND is still running. So basically what I need is
check if the room is booked in the selected dates
if it has been booked, check if its canceled
if it has been canceled, or not booked display it. Otherwise not
SELECT RoomNo, NightCost
FROM room, room_types, booking
WHERE typeid = fk1_typeid
and double_bed=1
and single_bed=0
AND canceled = '1' in
(SELECT canceled
from booking, room_booking
where bookingid = fk2_bookingid)
AND RoomNo not in
(SELECT fk1_RoomNo
FROM room_booking
WHERE '2010-04-02' between Check_in
and Check_Out or
'2010-04-03' between Check_in
and Check_Out) ;
I tried to be as clear as possible, i will be around to give more details if needed
