ขอสอบถามพี่ๆหน่อยครับ เรื่องสถานะการยกเลิกใบจองหน่อยครับ

-- เงื่อนไขปกติ 
SELECT  *
FROM room 
WHERE room_id NOT IN 
(
  (SELECT room_id FROM book r JOIN booklist e ON r.book_id = e.book_id
  WHERE r.book_status_cancel = '0' AND

                 ('2019-11-24' BETWEEN e.booklist_datein AND e.booklist_dateout)
                 OR
                 ('2019-11-26' BETWEEN e.booklist_datein AND e.booklist_dateout)
                 OR
                 (e.booklist_datein BETWEEN '2019-11-24' AND '2019-11-26')
                 OR
                 (e.booklist_dateout BETWEEN '2019-11-24' AND '2019-11-26')
	)
)

union 

--เงื่อนไข book_status_cancel = 1
SELECT  *
FROM room 
WHERE room_id  IN 
(
  (SELECT room_id FROM book r JOIN booklist e ON r.book_id = e.book_id
  WHERE r.book_status_cancel = '1' AND

                 ('2019-11-24' BETWEEN e.booklist_datein AND e.booklist_dateout)
                 OR
                 ('2019-11-26' BETWEEN e.booklist_datein AND e.booklist_dateout)
                 OR
                 (e.booklist_datein BETWEEN '2019-11-24' AND '2019-11-26')
                 OR
                 (e.booklist_dateout BETWEEN '2019-11-24' AND '2019-11-26')
	)

$sql = "SELECT * FROM room WHERE room_id NOT IN (
SELECT e.room_id FROM book r
JOIN booklist e ON r.book_id = e.book_id
WHERE r.book_status_deposit != '4' AND (
(e.booklist_datein BETWEEN '".$datein."' AND '".$dateout."')
OR ( e.booklist_dateout BETWEEN '".$datein."' AND '".$dateout."') OR 
('".$datein."' BETWEEN e.booklist_datein AND e.booklist_dateout) OR 
('".$dateout."' BETWEEN e.booklist_datein AND e.booklist_dateout)))";
$result = $db->query($sql);