选择未发送时事通讯的用户 [英] Selecting users who were not sent newsletter

问题描述

尝试创建查询以选择在批处理超时时未通过电子邮件发送新闻通讯的用户. mail_log表从3个不同的邮件列表中捕获条目，并保持过去4-5周的日志-换句话说，每个订户应有多个日志条目.

Trying to create a query to select users who were not emailed the newsletter when the batch times out. The mail_log table captures entries from 3 different mailing lists, and maintains the log for the past 4-5 weeks - in other words there should be multiple log entries for each subscriber.

我想选择在发送过程中批次超时时未通过电子邮件发送的所有订阅者.

I want to select all subscribers who were not emailed when the batch times out while sending.

mail_log
+--------+------------+-------------+------------+---------+
| log_id | send_date  | location_id | mailing_id | list_id |
+--------+------------+-------------+------------+---------+
location_id is which mailing list
mailing_id is the specific newsletter
list_id is the subscriber's id in the mailing list

mail_list 
+---------+-------+-------+-------+
| list_id | fname | lname | email |
+---------+-------+-------+-------+

我已经尝试过以下查询:

I have tried this query:

SELECT mail_list.*
FROM mail_list
LEFT JOIN mail_log ON mail_log.list_id = mail_list.list_id
WHERE mail_log.send_date = '2016-07-12'
AND mail_log.location_id = '2'
AND mail_log.list_id IS NULL`

查询返回0个结果，但成功查询应返回约700个结果.

The query returns 0 results, but a successful query should return about 700 results.

推荐答案

使用LEFT JOIN时，必须将对子表的限制放在ON子句中.否则，当您测试这些字段时，您将仅匹配非NULL行，这与AND mail_log.list_id IS NULL测试相反.

When you use LEFT JOIN, you have to put the restrictions on the child table into the ON clause. Otherwise, when you test those fields, you'll only match non-NULL rows, which contradicts the AND mail_log.list_id IS NULL test.

SELECT mail_list.*
FROM mail_list
LEFT JOIN mail_log ON mail_log.list_id = mail_list.list_id
    AND mail_log.send_date = '2016-07-12'
    AND mail_log.location_id = '2'
WHERE mail_log.list_id IS NULL

这篇关于选择未发送时事通讯的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！