Show all rows even when count is null 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?
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 !!!