这是您想要做的原型:
SELECT * FROM table1 t1
WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t1.id = t2.id)
这里,id被认为是两个表中的PK和FK.你应该相应调整.还要注意,在这种情况下比较PK和FK是很重要的.
所以,这里是你的查询应该如下所示:
SELECT id, room_name FROM rooms r
WHERE NOT EXISTS
(SELECT * FROM room_events re
WHERE
r.room_id = re.room_id
AND
(
room_start BETWEEN '1294727400' AND '1294729200'
OR
room_finish BETWEEN '1294727400' AND '1294729200')
)
如果需要,您可以通过在mysql客户端中执行查询来检查您的部分内容.例如,您可以确保以下内容是否返回任何记录:
SELECT * FROM room_events
WHERE room_start BETWEEN '1294727400' AND '1294729200'
OR
room_finish BETWEEN '1294727400' AND '1294729200'
如果没有,你已经发现了罪魁祸首,并采取相应的行动与其他部分:)