คำสั่งSQL
SELECT *
FROM tb_room
WHERE room_number NOT IN
(
SELECT
tb_booking_detail.room_num
FROM
tb_booking
INNER JOIN
tb_booking_detail on tb_booking.id = tb_booking_detail.ref_booking_id
WHERE
(date_start BETWEEN '2018-07-18' AND '2018-07-22')
OR
(date_end BETWEEN '2018-07-18' AND '2018-07-22')
OR
('2018-07-18' BETWEEN date_start AND date_end)
OR
('2018-07-22' BETWEEN date_start AND date_end )
)
ผมลองเปลี่ยนแล้วมันใช้ไม่ได้ครับ
$sql2= "SELECT * FROM tb_room WHERE room_number NOT IN
(SELECT tb_booking_detail.room_num
FROM
tb_booking
INNER JOIN
tb_booking_detail on tb_booking.id = tb_booking_detail.ref_booking_id
WHERE
(date_start BETWEEN '" . $start_date . "' AND '" . $end_date . "')
OR
(date_end BETWEEN '" . $start_date . "' AND '" . $end_date . "')
OR
('" . $start_date . "' BETWEEN date_start AND date_end)
OR
('" . $start_date . "' BETWEEN date_start AND date_end )
)";
<?php
$sql2= SQL<<<
SELECT *, '{$start_date}' as st, '{$end_date}' as en FROM
tb_room as tb WHERE not exists
(
SELECT room_num FROM tb_booking bk, tb_booking_detail dt
where dt.room_num = tb.room_number
and bk.id = dt.ref_booking_id
and (
(dt.date_start BETWEEN tb.st AND tb.en)
OR
(dt.date_end BETWEEN tb.st AND tb.en)
OR
( tb.st BETWEEN dt.date_start AND dt.date_end)
OR
( tb.en BETWEEN dt.date_start AND dt.date_end )
) limit 1
)
SQL;
สำหรับ condition not exists หรือ exists ก็เลือกใช้ตามงานเอานะครับ
และ จะทำงานไว ขึ้น ถ้ามีกำหนด index ของ room_num , start_date , end_date เอาไว้
Load balance : Server 00
