mysql:如果表B中不存在，请从表A中选择所有项目 [英] mysql: select all items from table A if not exist in table B

问题描述

我在从表a(id，room_name)中选择值时遇到问题，其中表b中没有相应的事件(room_id，room_start，room_finish)

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)

我的查询如下

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')

表a包含多个房间，表b包含房间事件 如果时间戳中的任何房间有任何事件，我不会得到任何结果.我希望所有房间都没有活动.

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.

推荐答案

以下是您要执行的操作的原型:

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)

此处，在两个表中都假定id是PK和FK.您应该相应地进行调整.还请注意，在这种情况下，比较PK和FK很重要.

Here, id is 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.

因此，这是查询的外观:

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')
          )

---

如果需要，可以通过在mysql客户端中执行查询来检查查询的各个部分.例如，您可以确保以下内容是否返回任何记录:

---

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:

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 :)

这篇关于mysql:如果表B中不存在，请从表A中选择所有项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！