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
– kasparg, Commented Oct 12, 2016 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. stackoverflow.com/a/58173579/4227722 — icortesi
– icortesi, Commented Sep 30, 2019 at 18:50

kasparg · Accepted Answer · 2016-10-12 12:27:51Z

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, 2016 at 12:27

kasparg

4283 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

fmc Over a year ago

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 Over a year ago

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.

Amelie Turgeon Over a year ago

In your posted data, you filter on p.memberid =1, but not in your query. It may affect your results.

fmc Over a year ago

@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!

Amelie Turgeon Over a year ago

@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.

|

Collectives™ on Stack Overflow

MySQL Left Join Query not working

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related