SQL LEFT OUTER JOIN subquery

Question

I have SQL data that looks like this:

events
id name         capacity
1  Cooking        10
2  Swimming       20
3  Archery        15

registrants
id  name
1   Jimmy
2   Billy
3   Sally

registrant_event
registrant_id  event_id
     1             3
     2             3
     3             2

I would like to select all of the fields in 'events' as well as an additional field that is the number of people who are currently registered for that event. In this case Archery would have 2 registrants, Swimming would have 1, and Cooking would have 0.

I imagine this could be accomplished in a single query but I'm not sure of the correct syntax. How would a query be written to get that data?

Answer 1

SELECT e.*, ISNULL(ec.TotalRegistrants, 0) FROM events e LEFT OUTER JOIN
(
   SELECT event_id, Count(registrant_id) AS TotalRegistrants
   FROM registrant_event
   GROUP BY event_id
) ec ON e.id  = ec.event_id