我有两个表:tblAPARTMENT
和tblRESERVATION
。我想要所有不在这些日期的预订表中的apartmentID的列表。
我试过了:
SELECT apartmentID, apartmentNAME
FROM tblAPARTMENT
NOT IN
(SELECT apartmentID FROM tblRESERVATION
WHERE tblRESERVATION.startDate > _a-date_
AND tblRESERVATION.endDate < _a-date_
我知道有一种更好的方法可以编写此代码,但我无法弄清楚。
语法上正确的查询写方法是:
SELECT a.apartmentID, a.apartmentNAME
FROM tblAPARTMENT a
WHERE a.apartmentID NOT IN (SELECT a.apartmentID
FROM tblRESERVATION r
WHERE r.startDate <= _a-date_ AND r.endDate >= _a-date_
);
这是一种合理的方法,但是使用它通常not exists
比not in
(由于NULL
处理)更安全:
SELECT a.apartmentID, a.apartmentNAME
FROM tblAPARTMENT a
WHERE NOT EXISTS (SELECT a.apartmentID
FROM tblRESERVATION r
WHERE r.startDate <= _a-date_ AND r.endDate >= _a-date_ and
r.apartmentid = a.apartmentid
);
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句