MySQL-选择收集第二个选择结果的地方

Question

我有这个问题。第二部分是获取正确的值，但最终输出数据仍然包括列等于选择值的行。

SELECT * 
FROM roomfacilities 
WHERE room <> '(SELECT room_assigned 
                FROM allocation 
                WHERE booking_id = 01010106)';

我试图让它工作，说 SELECT all from roomfacilities WHERE room 'does not equal' result from select.

感谢您的帮助。

Answer 1

使用 NOT IN 谓词:

SELECT *
FROM roomfacilities 
WHERE room NOT IN(SELECT room_assigned 
                  FROM allocation 
                  WHERE booking_id = 01010106);

或者: LEFT JOIN:

SELECT f.*
FROM roomfacilities f
LEFT JOIN allocation a  ON f.room       = a.room_assigned 
                       AND a.booking_id = 01010106
WHERE a.room_assigned IS NULL;