MySQL Left Join Query not working

Question

The history table contains dates that I'm trying to match against the participation table. If the date doesn't exist in the participation table, then I want the record(s) pulled out so I can enter the participation data . But what I have doesn't work. Here's a rundown of what I'm using:

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 |
+----------+------------+

And my left join query:

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 |
+-----------+------------+-------------+----------+----------+

The groupdate field should be NULL except for memberid 2 and 4. Plus it gives the data twice. What am I doing wrong?

Best regards.

UPDATE

Per the request of 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 |
+--------+-----------------------------------------------+------------+----------+----------+

UPDATE

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)

I hard-coded h.clientid = "1" and still get nothing. And that record should return a NULL value for groupdate.

can you show what is inside participation table? SELECT * FROM participation limit 10; — kasparg, Oct 12 '16 at 11:59
I was having the same problem with mariadb. I found that doing two subqueries was the only way of getting the data the way I wanted, afaik this wasn't necesary with mysql. https://stackoverflow.com/a/58173579/4227722 — icortesi, Sep 30 '19 at 18:50

score 1 · Accepted Answer · answered Oct 12 '16 at 12:27

1

If I understand you correctly, you want to get records where participation date is missing. Add an additional criteria to the WHERE clause: AND p.groupdate IS NULL

Also, note that you are joining participation only on groupdate, join it also on clientid, like this: LEFT JOIN participation AS p ON p.groupdate = h.attend_date and p.clientid = h.clientid

answered Oct 12 '16 at 12:27

kasparg

428
3
14

1

You're assessment of my query is correct. Please see my update. What you showed me looks right. All of the query looks right...to me. But it fails every time. – fmc Oct 12 '16 at 12:52
Sorry. I made the changes in the changes to the `php` page and it still doesn't pull out the `NULL` records. This is puzzling, to say the least. – fmc Oct 12 '16 at 13:02
In your posted data, you filter on p.memberid =1, but not in your query. It may affect your results. – Amelie Turgeon Oct 13 '16 at 05:07
@AmelieTurgeon - The memberid is stored in both tables alongside the clientid for the clients. Please keep in mind that I've hard-coded `memberid = "1"` and `clientid = "1"` for example purposes. The production queries obviously use variables. That said, I'll throw out what I have and start anew. I see no other way to go. The last UPDATE in my Post, that query should yield a record result, but it doesn't. And that's what has me puzzled. So crazy! – fmc Oct 13 '16 at 05:51
@Landslyde then don't forget to join on clientid and memberid as well as the date. And if it still doesn't work, select for exploration purpose p.* and h.*. You may understand what goes wrong. – Amelie Turgeon Oct 13 '16 at 13:00
@AmelieTurgeon - I actually reworked everything and have it running smoothly now. Thank you for your input though. I appreciate you for that. By the way, and I didn't mention this, this particular query is for posting the same `notes` to all the clients at once...a mass posting. That's why I wasn't using the `clientid`. – fmc Oct 13 '16 at 13:57

MySQL Left Join Query not working

1 Answers1