MySQL:查找不匹配的记录(JOIN/IS NULL)

Question

我正在尝试为这些简化的 MySQL 表构建查询:

==campaigns==  
id  
content  

==lists==  
id  
name  

==subscriptions==  
id  
list_id   
subscriber_id  

==events==  
id  
campaign_id
list_id
subscription_id

==triggers==
id
campaign_id
list_id
time_to_send

我想查找所有订阅的 subscription_id，而某个 Campaign_id 没有对应的事件。

此查询返回所有没有事件的订阅:

选择*
FROM 订阅 AS
LEFT JOIN 事件 AS e
ON s.id = e.subscription_id
WHERE e.subscription_id IS NULL

但是，我想返回没有特定广告事件的事件或没有特定触发器的事件的所有订阅。

我该怎么做？

Answer 1

But, I want to return all subscriptions without an event for a particular campaign

由于订阅和事件之间的关系是由事件定义的，因此这是不可能的。

So, there could be an entry in the events table that we sent campaign #1, but we want to know if this subscription has ever been sent campaign #2 specifically

如果您想识别与营销事件 #1 相关但与营销事件 #2 无关的订阅，那么这是一个完全不同的问题......

SELECT s.*
FROM subscriptions s
INNER JOIN events e
  ON s.id=e.subscription_id
INNER JOIN campaigns c
  ON e.campaign_id=c.id
WHERE c.content='campaign #1'
AND NOT EXISTS (
   SELECT 1
   FROM events e2
   INNER JOIN capaigns c2
     ON e2.campaign_id=c2.id
   WHERE c2.content='campaign #2'
   AND e2.subscription_id=e.subscription_id
);