How do I include filtered rowcounts from two other tables in a query of a third?

Question

I have a MySql database with three tables I need to combine in a query: schedule, enrolled and waitlist. They are implementing a basic class enrollment system.

Schedule contains the scheduled classes. When a user enrolls in a class, their user account id and the id of the scheduled class are stored in enrolled. If a class is at capacity, they are stored in waitlist instead. All three tables share a scheduleId column which identifies each class.

When I query the schedule table, I need to also return enrolled and waitlist columns that represent the number of users enrolled and waiting for that particular scheduleId.

A preliminary query I came up with to accomplish this was:

select s.id, s.classDate, s.instructor, COUNT(e.id) as enrolled
from schedule as s
left outer join enrolled as e
on s.id = e.scheduleId
group by s.id

which works ok for one or the other, but obviously I can't get the values for both the enrolled and waitlist tables this way. Can anybody suggest a good way of doing this?

Answer 1

Two quick ways:

1- Use COUNT(DISTINCT e.id), COUNT(DISTINCT w.id) to get the number of unique instances in each table, then join on both. This is possibly hideously inefficient.

2- Use subqueries in the FROM clause (only works in MySQL 5.0 and later):

SELECT s.id, s.classDate, s.instructor, tmpE.c AS enrolled, tmpW.c AS waiting
FROM
  schedule AS s,
  ( SELECT scheduleID, COUNT(*) AS c FROM enrolled GROUP BY scheduleID ) AS tmpE,
  ( SELECT scheduleID, COUNT(*) AS c FROM waiting GROUP BY scheduleID ) AS tmpW
WHERE
    s.id = e.scheduleID
    AND s.id = w.scheduleID
GROUP BY s.id

I may have missed a left join in there, though.

Answer 2

Use nested SELECT queries. Assuming a bit about your schema, how about something like this (might not work on some flavors of SQL):

select s.id, s.classDate, s.instructor, 
       (select COUNT(e.id) from enrolled e where e.scheduleId = s.id) as enrolled,
       (select COUNT(w.id) from waitlist w where w.scheduleId = s.id) as waiting
from schedule as s
group by s.id

Answer 3

I would do it with another left join and two inline count(distincts)

select s.id, s.classDate, s.instructor ,
count(distinct e.id) as enrolled,
count(distinct w.id) as waiting
from schedule as s
left outer join enrolled as e
on s.id = e.scheduleID
left outer join waitlist as w
on s.id = w.scheduleID
group by s.id

When I ran this approach versus the subqueries it executed about twice as fast, but I am looking at a pretty small result set.