MySql 选择第三个表中不存在的连接行

Question

我在创建一个 SQL 查询时遇到困难，该查询选择 2 个表的联接，而第三个表中不存在关系。

为了更具体，我尝试用一​​个例子来解释它。我有一个表成员、表单和member_form。当成员在表单中插入答案时，该答案将保存在member_form 表中。这些表格可以是活跃的，并且有截止日期。所以我想要的是为每个成员返回一个结果以及他仍未填写的表格。但是表格必须是事件的，并且截止日期必须是例如2018-03-15。我不知道成员或表格的数量。所以我不能说 from select * where member id is = x.这是为了向所有尚未在特定日期填写表单的用户发送提醒邮件所必需的。

member
id  |   name    |   email
--------------------------
1   |   Test    |   test@email.com
2   |   test2   |   test2@email.com
4   |   test4   |   test4@email.com
5   |   test5   |   test5@email.com
6   |   test6   |   test6@email.com
7   |   test7   |   test7@email.com


form
id  |   insert_date         |   deadline_date       |   active
---------------------------------------------------------------
1   |   2018-03-15 00:00:00 |   2018-03-15 00:00:00 |   1   
2   |   2018-02-10 00:00:00 |   2018-05-15 00:00:00 |   0   
3   |   2018-03-15 00:00:00 |   2018-03-15 00:00:00 |   1   
5   |   2018-03-15 00:00:00 |   2018-06-15 00:00:00 |   1   
6   |   2018-03-15 00:00:00 |   2018-05-15 00:00:00 |   1   
7   |   2018-03-15 00:00:00 |   2018-04-15 00:00:00 |   0       


member_form
member_id   |   form_id     |   answer
--------------------------------------
1           |   6           |   1
1           |   2           |   2
1           |   5           |   1
2           |   2           |   1
2           |   3           |   1
4           |   6           |   2
5           |   6           |   3
5           |   7           |   2
6           |   1           |   2
7           |   2           |   1


Result
member_id   |   name    |   email           |   form_id |   insert_date         |   deadline_date       |   active
-------------------------------------------------------------------------------------------------------------------
2           |   test2   |   test2@email.com |   6       |   2018-03-15 00:00:00 |   2018-05-15 00:00:00 |   1
6           |   test6   |   test6@email.com |   6       |   2018-03-15 00:00:00 |   2018-05-15 00:00:00 |   1
7           |   test7   |   test7@email.com |   6       |   2018-03-15 00:00:00 |   2018-05-15 00:00:00 |   1

Answer 1

SELECT * FROM form AS f , member AS m WHERE f.deadline_date = '2018-05-15 00:00:00' AND f.active = 1 and !exists(select * FROM member_form AS amf WHERE amf.member_id = am.id And amf.form_id = af.id);