我对sql查询有问题
我的问题是:
SELECT `rooms`.*
FROM `rooms`
WHERE (`rooms`.`room_id` NOT IN (
SELECT *
FROM `reservations` `res`
WHERE ('2012-05-03' NOT BETWEEN res.expected_checkin_date
AND res.expected_checkout_date)
AND ('2012-05-08' NOT BETWEEN res.expected_checkin_date
AND res.expected_checkout_date)))
问题是,当第二个
select
返回null时,它显示以下错误:#1241 - Operand should contain 1 column(s)
有人能帮我吗?
EDIT
当我运行这个查询时,它会显示所有的行,甚至包括第二个
select
我不知道为什么?
SELECT `rooms`.*, `type`.*, `status`.* FROM `rooms` INNER JOIN `room_types` AS `type` ON `type`.`room_type_id` = `rooms`.`room_type_id` AND `type`.`num_beds` >= 1 AND type.room_type_id=2 INNER JOIN `room_statuses` AS `status` ON `status`.`room_status_id` = `rooms`.`room_status_id` WHERE (`rooms`.`room_id` NOT IN (SELECT `res`.`room_id` FROM `reservations` `res` WHERE (res.expected_checkin_date >= '2012-05-01' AND res.expected_checkin_date < '2012-05-31') AND (res.expected_checkout_date >= '2012-05-31' AND res.expected_checkout_date < '2012-05-31')))
最佳答案
我想你想
SELECT `reservations`.`room_id` FROM `reservations`
对于子选择,因为
room_id
是与NOT IN
运算符匹配的对象。