我在reservation
MySQL数据库的表中有以下两条记录.
当我使用此查询来获取可用的车辆时,它似乎给了我每辆车而不仅仅是指定日期之间不在预订表中的车辆.
这是查询:
SELECT v.id, v.brand, v.type, v.description, v.airco, v.seats, v.hourly_rent FROM vehicle as v WHERE v.id NOT IN( (SELECT v.id FROM vehicle as v LEFT JOIN reservation as r on r.`vehicle_id` = v.id WHERE r.status_id in(3,4,5) AND ( ( ('2014-01-01' >= r.startdate AND '2014-01-03' <= r.enddate ) OR ('2014-01-01' <= r.startdate AND '2014-01-03' >= r.enddate ) ) OR ( ('2014-01-01' >= r.startdate AND '2014-01-01' <= r.enddate) OR ('2014-01-03' >= r.startdate AND '2014-01-03' <= r.enddate) ) ) GROUP BY v.id ) )
当我使用此查询时:
SELECT * FROM reservation as r WHERE ( ( ('2014-01-01' >= r.startdate AND '2014-01-03' <= r.enddate ) OR ('2014-01-01' <= r.startdate AND '2014-01-03' >= r.enddate ) ) OR ( ('2014-01-01' >= r.startdate AND '2014-01-01' <= r.enddate) OR ('2014-01-03' >= r.startdate AND '2014-01-03' <= r.enddate) ) )
要获得预订,我可以在图片中看到1条记录.
什么可能是错的,我该如何解决这个问题?
试试这个 -
SELECT v.id, v.brand, v.type, v.description, v.airco, v.seats, v.hourly_rent FROM vehicle AS v LEFT JOIN reservation AS r ON v.id = r.vehicle_id AND r.startdate <= '2014-01-03' AND r.enddate >= '2014-01-01' WHERE r.id IS NULL;
或者您的状态条目
SELECT v.id, v.brand, v.type, v.description, v.airco, v.seats, v.hourly_rent FROM vehicle AS v LEFT JOIN reservation AS r ON v.id = r.vehicle_id AND r.startdate <= '2014-01-03' AND r.enddate >= '2014-01-01' AND r.status NOT IN (3,4,5) WHERE r.id IS NULL;