1

I want to see all the rows from one table and then do a count on the second table to see how many match, but want to see it all even if there are 0 matches in the second table.

My query:

SELECT
 a.area_name,
 COUNT(b.id) AS cnt
FROM areas a
LEFT JOIN events b ON a.area_id = b.i_area
WHERE YEAR(b.i_date) = YEAR(CURRENT_DATE()) AND MONTH(b.i_date) = MONTH(CURRENT_DATE())
GROUP BY a.area_name

Example desired output:

area 1 | 1
area 2 | 0
area 3 | 5

What I currently get is:

area 1 | 1
area 3 | 5

I tried CROSS and RIGHT join as well, just to make sure, but I don't get the desired effect.

What am I missing or doing wrong?

1
  • The ON clause says how the tables are related; the WHERE is for filtering. – Rick James Apr 29 '20 at 4:07
1

You can do this first: REMOVE THE WHERE CLAUSE

SELECT
 a.area_name,
 COUNT(b.id) AS cnt
FROM areas a
LEFT JOIN events b ON a.area_id = b.i_area
GROUP BY a.area_name

This will show area 2

If you really want the WHERE clause, move it into the ON Clause

SELECT
 a.area_name,
 COUNT(b.id) AS cnt
FROM areas a
LEFT JOIN events b ON
    a.area_id = b.i_area AND
    YEAR(b.i_date) = YEAR(CURRENT_DATE()) AND
    MONTH(b.i_date) = MONTH(CURRENT_DATE())
GROUP BY a.area_name

GIVE IT A TRY !!!

1
  • Moving the WHERE into ON forces MySQL to materialize every row from the areas table – RolandoMySQLDBA Apr 23 '20 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.