How to create conditions in mysql (using "if")?

Question

This code works great to find an available room for a specific date, but it doesn’t work to show the number that has been booked and canceled.

"Hotel" has 4 rooms and 1 of them is booked canceled

So, even if I do the undo, the select method continues to give me 3 results. Maybe because the second one And it still works. So basically I need

• check if the room is booked on selected dates.
• if it has been booked, check if it is canceled
• if it was canceled or not registered, 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 there to provide more detailed information if necessary

Answer 1

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
        )

Answer 2

• 2010-04-02 2010-04-04,

  • - 2010-04-03
  • - 2010-04-02
  • room_booking .
    

• ,

---

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

, .. : -)