postgreSQL JOIN AND NOT EXISTS

Question

comms                       
+-------------+
|    uuid     |
+-------------+
| comm_uuid_1 |
| comm_uuid_2 |
+-------------+
events
+--------------+-------------+
|     uuid     |  comm_uuid  |
+--------------+-------------+
| event_uuid_1 | comm_uuid_1 |
| event_uuid_2 | comm_uuid_1 |
+--------------+-------------+
actions
+---------------+-------+---------------------+
|     uuid      | type  |     event_uuid      |
+---------------+-------+---------------------+
| random_uuid_1 | stack | event_uuid_1        |
| random_uuid_2 | pop   | event_uuid_1        |
+---------------+-------+---------------------+

基本上我试图得到所有的通信，他们的事件匹配一定的标准，但过滤器是从事件的动作，这是我使用的查询:

SELECT * 
FROM comms JOIN events ON events.comm_uuid = comms.uuid 
AND (EXISTS (SELECT 1 FROM actions
WHERE events.uuid = actions.event_uuid AND actions.type = 'stack')) 
AND (not EXISTS (SELECT 1 FROM actions
WHERE events.uuid = actions.event_uuid AND actions.type = 'pop')) 

在这种情况下，查询应该不返回任何东西，因为有带有动作的事件。类型=堆栈，但也有事件在相同的通信与动作。Type = pop，出于某种原因，它返回给我与动作相关的通信。type = stack，我只在没有动作的情况下才需要它。在同一通信中的事件中键入= pop

Answer 1

SELECT * FROM comms 
WHERE 
EXISTS (
SELECT 1 FROM actions
JOIN events on (events.uuid = actions.event_uuid)
WHERE events.comm_uuid = comms.uuid 
AND actions.type = 'stack') 
AND NOT EXISTS ( 
SELECT 1 FROM actions
JOIN events on (events.uuid = actions.event_uuid)
WHERE events.comm_uuid = comms.uuid 
AND actions.type = 'pop'
)