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