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

1. check if the room is booked in the selected dates
2. if it has been booked, check if its canceled
3. 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