mysql：如果表 B 中不存在，则选择表 A 中的所有项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4660871/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql: select all items from table A if not exist in table B
提问by m1k3y3
I am having problem with selecting values from table a (id, room_name) where there are no corresponding events in table b (room_id, room_start, room_finish)
我在从表 a (id, room_name) 中选择值时遇到问题，表 b (room_id, room_start, room_finish) 中没有相应的事件
my query looks following
我的查询如下
SELECT id, room_name FROM rooms
WHERE NOT EXISTS
(SELECT * FROM room_events
WHERE room_start BETWEEN '1294727400' AND '1294729200'
OR
room_finish BETWEEN '1294727400' AND '1294729200')
table a contains multiple rooms, table b contains room events I am getting no results in case there is any event for any of the rooms within the timestamps. I am expecting all rooms having NO events.
表 a 包含多个房间，表 b 包含房间事件，如果时间戳内的任何房间有任何事件，我将不会得到任何结果。我期待所有房间都没有活动。
回答by Sarfraz
Here is the prototype for what you want to do:
这是您要执行的操作的原型：
SELECT * FROM table1 t1
WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t1.id = t2.id)
Here, idis assumed to be the PK and FK in both tables. You should adjust accordingly. Notice also that it is important to compare PK and FK in this case.
此处，id假定为两个表中的 PK 和 FK。你应该相应地调整。另请注意，在这种情况下比较 PK 和 FK 很重要。
So, here is how your query should look like:
因此，您的查询应如下所示：
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')
)
If you want, you check the parts of your query by executing them in mysql client. For example, you can make sure if the following returns any records or not:
如果需要，您可以通过在 mysql 客户端中执行来检查查询的各个部分。例如，您可以确定以下是否返回任何记录：
SELECT * FROM room_events
WHERE room_start BETWEEN '1294727400' AND '1294729200'
OR
room_finish BETWEEN '1294727400' AND '1294729200'
If it doesn't, you have found the culprit and act accordingly with other parts :)
如果没有，您已经找到了罪魁祸首，并对其他部分采取了相应的行动:)
回答by Santiago Alessandri
You are missing to use only the events from that room. That is done by matching the id.
您无法仅使用该房间的事件。这是通过匹配 id 来完成的。
SELECT id, room_name FROM rooms r
WHERE NOT EXISTS
(SELECT * FROM room_events re
WHERE r.id = re.room_id AND
room_start BETWEEN '1294727400' AND '1294729200'
OR
room_finish BETWEEN '1294727400' AND '1294729200')
