SQL: Get count of rows returned from a left join
- by Rogue Coder
I have two tables, one called calendars and one called events. There can be multiple calendars, and multiple events in a calendar. I want to select every calendar, also getting the number of events in the calendar.
Right now I have :
SELECT C.*, COUNT(*) AS events FROM `calendars` AS C
LEFT JOIN `events` E ON C.ID=E.calendar
GROUP BY C.ID
But that doesn't work. Items with no events still return 1. Any ideas?