How to create conditions in mysql (use of 'if')?

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 cancel开发者_如何学JAVAed

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

SELECT  *
FROM    room
JOIN    room_types
ON      typeid = fk1_typeid
WHERE   double_bed = 1
        AND single_bed = 0
        AND roomNo NOT IN
        (
        SELECT  fk1_roomno
        FROM    room_booking
        WHERE   check_out >= '2010-04-02'
                AND check_in <= '2010-04-03'
                AND NOT canceled
        )

1. A room is occupied between 2010-04-02 and 2010-04-04 if

   • someone is to be checked in before 2010-04-03
   • and that someone is not to be check out before 2010-04-02
   • and that room_booking is not canceled.
     

2. A room is free, when it is not occupied

 SELECT R.RoomNo, R.NightCost
   FROM room as R
   JOIN room_types as RT ON RT.typeid = R.fk1_typeid
 WHERE (R.single_bed=0 and R.double_bed=1)
   AND R.roomNo NOT IN (
        SELECT RB.fk1_roomno
          FROM room_booking as RB
         WHERE (RB.check_out >= '2010-04-02')
           AND (RB.check_in  <= '2010-04-03')
           AND NOT (RB.canceled=1)
    )     

Which is exactly what Quassnoi said before me .. sigh :-)