'SQL LEFT OUTER JOIN subquery

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?

sqldatabasecountsubquery


Solution 1:[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

Solution 2:[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

Solution 3:[3]

select d.id1, d.name, d.cappacity, count(d.b_id) as number_of_people
from (select eve.id1,eve.name,eve.cappacity,re_eve.b_id
     from eve left join re_eve on eve.id1 = re_eve.b_id) d
group by d.id1, d.name, d.cappacity

Solution 4:[4]

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

Solution 5:[5]

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

Solution 6:[6]

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

Solution 7:[7]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 devio
Solution 3 Volker Stolz
Solution 4 Powerlord
Solution 5
Solution 6 Joel Coehoorn
Solution 7

Related Questions