MySQL左转联接查询无法工作

Question

history table包含我试图与participation table匹配的日期。如果participation table中不存在日期，那么我希望将记录提取出来，以便输入参与数据。但我的东西不管用。下面是我正在使用的内容的简要介绍：

MariaDB [sotp]> describe history;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| historyid    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| amount       | float            | NO   |     | NULL    |                |
| subsidy      | char(1)          | NO   |     | NULL    |                |
| last_payment | date             | NO   |     | NULL    |                |
| amount_paid  | float            | NO   |     | NULL    |                |
| balance      | float            | NO   |     | NULL    |                |
| attend       | char(1)          | NO   |     | N       |                |
| atend_date   | date             | NO   |     | NULL    |                |
| groupid      | int(11) unsigned | NO   |     | NULL    |                |
| clientid     | int(10) unsigned | NO   | MUL | NULL    |                |
| memberid     | int(10) unsigned | NO   | MUL | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

MariaDB [sotp]> select clientid, attend_date
-> from history
-> where memberid = "1"
-> AND MONTH(attend_date) = "10"
-> AND YEAR(attend_date) = "2016"
-> AND attend_date <> "0000-00-00"
-> ORDER BY attend_date ASC;
+----------+-------------+
| clientid | attend_date |
+----------+-------------+
|        3 | 2016-10-11  |
|        1 | 2016-10-11  |
|        7 | 2016-10-11  |
|        2 | 2016-10-11  |
|        4 | 2016-10-11  |
|        5 | 2016-10-11  |
|        8 | 2016-10-11  |
|        9 | 2016-10-11  |
+----------+-------------+

MariaDB [sotp]> describe participation;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| partid    | int(11)          | NO   | PRI | NULL    | auto_increment |
| notes     | varchar(255)     | NO   |     | NULL    |                |
| groupdate | date             | NO   |     | NULL    |                |
| clientid  | int(10) unsigned | NO   | MUL | NULL    |                |
| memberid  | int(10) unsigned | NO   | MUL | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

MariaDB [sotp]> select clientid, groupdate
-> from participation
-> where memberid = "1"
-> AND MONTH(groupdate) = "10"
-> AND YEAR(groupdate) = "2016"
-> AND groupdate <> "0000-00-00"
-> ORDER BY groupdate ASC;
+----------+------------+
| clientid | groupdate  |
+----------+------------+
|        2 | 2016-10-11 |
|        4 | 2016-10-11 |
+----------+------------+

还有我的left join查询：

SELECT historyid, p.groupdate, h.attend_date, h.clientid, h.memberid
FROM history AS h
LEFT JOIN  participation AS p  ON p.groupdate = h.attend_date
WHERE h.memberid = "1"
AND MONTH(h.attend_date) = "10"
AND YEAR(h.attend_date) = "2016"
AND h.attend_date <> "0000-00-00"
ORDER BY h.attend_date ASC;
+-----------+------------+-------------+----------+----------+
| historyid | groupdate  | attend_date | clientid | memberid |
+-----------+------------+-------------+----------+----------+
|        58 | 2016-10-11 | 2016-10-11  |        3 |        1 |
|        61 | 2016-10-11 | 2016-10-11  |        2 |        1 |
|        59 | 2016-10-11 | 2016-10-11  |        1 |        1 |
|        62 | 2016-10-11 | 2016-10-11  |        4 |        1 |
|        60 | 2016-10-11 | 2016-10-11  |        7 |        1 |
|        63 | 2016-10-11 | 2016-10-11  |        5 |        1 |
|        61 | 2016-10-11 | 2016-10-11  |        2 |        1 |
|        64 | 2016-10-11 | 2016-10-11  |        8 |        1 |
|        62 | 2016-10-11 | 2016-10-11  |        4 |        1 |
|        65 | 2016-10-11 | 2016-10-11  |        9 |        1 |
|        63 | 2016-10-11 | 2016-10-11  |        5 |        1 |
|        64 | 2016-10-11 | 2016-10-11  |        8 |        1 |
|        65 | 2016-10-11 | 2016-10-11  |        9 |        1 |
|        58 | 2016-10-11 | 2016-10-11  |        3 |        1 |
|        59 | 2016-10-11 | 2016-10-11  |        1 |        1 |
|        60 | 2016-10-11 | 2016-10-11  |        7 |        1 |
+-----------+------------+-------------+----------+----------+

groupdate字段应该是NULL，除了memberid 2和4之外。另外，它提供了两次数据。我做错了什么？

诚挚的问候。

更新

根据kasparg的要求

MariaDB [sotp]> select *
-> from participation;
+--------+-----------------------------------------------+------------+----------+----------+
| partid | notes                                         | groupdate  | clientid | memberid |
+--------+-----------------------------------------------+------------+----------+----------+
|    824 | aaaaaaaaaaaaaaaaaaaaaazzzzzzzzzzzzzzzzzzzzzzz | 2016-01-26 |        3 |        1 |
|    825 | lol hahaha and stuff                          | 2016-01-26 |        4 |        1 |
|    826 | aaaaaaaaaaaaaaaaaaaaaa                        | 2016-01-26 |        2 |        1 |
|    827 | zzzzzzzzzzzzzzaaaaaaaaaaaaaaaaaa              | 2016-01-26 |        1 |        1 |
|    828 | llllllllllllllllllllllllllllllllllll          | 2016-01-28 |        3 |        1 |
|    829 | bbbbbbbbbbbbbbbbbbb                           | 2016-01-28 |        1 |        1 |
|    830 | Absent                                        | 2016-01-28 |        4 |        1 |
|    831 | Absent                                        | 2016-01-28 |        2 |        1 |
|    832 | llllkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk           | 2016-01-29 |        5 |        1 |
|    833 | xxxxxxxxxxxxxxxzzzzzzzzzzzzzzzzzz             | 2016-01-29 |        4 |        1 |
|    834 | xxxxxxxxxxxxxxxxxxxxxxxx                      | 2016-01-29 |        2 |        1 |
|    835 | ccccccccccccccccccccccc                       | 2016-01-29 |        1 |        1 |
|    836 | llllkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk           | 2016-01-29 |        3 |        1 |
|   1063 | zzzzzzzzzzzzzzzzzzzzzzzzzzzz                  | 2016-01-30 |        3 |        1 |
|   1064 | ddddddddddddddddddddddddd                     | 2016-01-30 |        1 |        1 |
|   1065 | No entry made.                                | 2016-01-30 |        4 |        1 |
|   1066 | No entry made.                                | 2016-01-30 |        2 |        1 |
|   1075 | 2016-02-26: car wreck                         | 2016-10-11 |        2 |        1 |
|   1076 | 2016-02-26: broken legs                       | 2016-10-11 |        4 |        1 |
+--------+-----------------------------------------------+------------+----------+----------+

更新

MariaDB [sotp]> SELECT historyid, p.groupdate,  h.attend_date, p.clientid, h.clientid, h.memberid
-> FROM history AS h
-> LEFT JOIN  participation AS p  ON p.groupdate = h.attend_date and p.clientid = h.clientid
-> WHERE h.memberid = "1"
-> AND h.clientid = "1"
-> AND MONTH(h.attend_date) = "10"
-> AND YEAR(h.attend_date) = "2016"
-> AND h.attend_date <> "0000-00-00"
-> AND p.groupdate = "NULL"
-> ORDER BY h.attend_date ASC;
Empty set, 1 warning (0.00 sec)

我对h.clientid = "1"进行了硬编码，仍然一无所获。该记录应该返回groupdate的一个groupdate值。

Answer 1

如果我对你的理解正确，你想得到的记录，参与日期是缺失的。在WHERE子句中添加一个附加条件：AND p.groupdate IS NULL

另外，请注意，您只在groupdate上加入，也可以在clientid上加入它，如：LEFT JOIN participation AS p ON p.groupdate = h.attend_date and p.clientid = h.clientid