gpt4 book ai didi

mysql - 预订 : I want to display all records with a condition including if the record is null in table number 2

转载 作者:行者123 更新时间:2023-11-29 07:20:10 24 4
gpt4 key购买 nike

我正在开发一个预订系统,我将向用户显示所有状态为 Verified 的记录,carType 与用户想要的相同,以及与用户输入的服务类型相同的记录。问题是,如果汽车记录在预订表中没有现有记录,则记录不会显示。重叠日期的检查器有效,但它不显示已验证的其他记录、相同的服务类型、相同的车型但在预订表中为空。

    $startDate = '2019-06-04 07:00:00';
$endDate = '2019-06-20 07:00:00';
$serviceType = "Self-drive";
$carType = "SUV";


$sql = "SELECT carrecord.recordID, carrecord.recordLong, carrecord.recordLat,
model.brandName, model.modelName, carrecord.amount, model.carType, carrecord.status,
carrecord.recordPicture, booking.recordID AS bookRecordID FROM carrecord
LEFT JOIN booking
ON booking.recordID = carrecord.recordID
LEFT JOIN car
ON car.carID = carrecord.carID
LEFT JOIN model
ON model.modelID = car.modelID
WHERE
model.carType = '$carType'
AND
carrecord.serviceType = '$serviceType'
AND
carrecord.status = 'Verified'
OR booking.recordID IS NULL
AND NOT EXISTS
(
SELECT booking.recordID FROM booking
WHERE '$startDate' <= booking.endDate
AND '$endDate' >= booking.startDate
AND booking.status = 'Accepted'
)";

最佳答案

更改 booking.recordID IS NULL,model.carType = '$carType' 条件并将它们移到 on 子句中

SELECT carrecord.recordID, carrecord.recordLong, carrecord.recordLat, 
model.brandName, model.modelName, carrecord.amount, model.carType, carrecord.status,
carrecord.recordPicture, booking.recordID AS bookRecordID
FROM carrecord
LEFT JOIN booking
ON booking.recordID = carrecord.recordID
LEFT JOIN car
ON car.carID = carrecord.carID
LEFT JOIN model
ON model.modelID = car.modelID and model.carType = '$carType'
WHERE carrecord.serviceType = '$serviceType'
AND
carrecord.status = 'Verified'

NOT EXISTS
(
SELECT booking.recordID FROM booking
WHERE '$startDate' <= booking.endDate
AND '$endDate' >= booking.startDate
AND booking.status = 'Accepted'
)

关于mysql - 预订 : I want to display all records with a condition including if the record is null in table number 2,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/56680741/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com