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

Update: Thanks for the great answers, you all rock!

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

Answer 2

SELECT Events.ID, Events.Name, Events.Capacity, 
       ISNULL(COUNT(Registrant_Event.Registrant_ID), 0)
FROM Events
LEFT OUTER JOIN Registrant_Event ON Events.ID = Registrant_Event.Event_ID
GROUP BY Events.ID, Events.Name, Events.Capacity

Answer 3

select e.id, e.name, e.capacity, IsNull(re.eventCount,0) from events e
left join (
 select count(event_id) as eventCount, event_id  from registrant_event group by event_id
 ) re 
on e.id = re.event_id

Answer 4

SELECT
  events.*
, COUNT(registrant_event.registrant_id) AS registrantsCount
FROM events
LEFT JOIN registrant_event ON events.id = registrant_event.event_id
GROUP BY events.id

Answer 5

SELECT e.id, count(*) AS NumRegistrants
FROM events e
LEFT JOIN registrant_event re ON re.event_id=e.id
GROUP BY e.id

Note that this will return 1 instead of 0 for events with no registrants. To get it to show 0 instead, you have to get a little more complicated:

SELECT *,
    (SELECT COUNT(*) FROM registrant_event WHERE event_id=id) AS NumRegistrants
FROM events

Answer 6

I tested this in Oracle 11g, and it seems to work

SELECT e.id, e.name, e.capacity, COUNT(re.event_id)
FROM events e
LEFT JOIN registrant_event re
  ON e.id = re.event_id
GROUP BY e.id, e.name, e.capacity