mysql: select all items from table A if not exist in table B
I am having prob开发者_如何学运维lem with selecting values from table a (id, room_name) where there are no corresponding events in table 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.
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, 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')
)
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 :)
You are missing to use only the events from that room. That is done by matching the 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')
精彩评论