Outer Join Syntax Problems (Multiple Tables)

Hello all--
I'm trying to run a SELECT on 3 tables:Class,Enrolled,Waiting.
I want to select the name of the class, the count of the students enrolled, and the count of the students waiting to enroll.
My current query...
SELECT Class.Name, COUNT(Enrolled.StudentID) AS EnrolledCount, COUNT(Waiting.StudentID) AS WaitingCount
FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID
GROUP BY Class.Name
...results in identical counts for enrolled and waiting, which I knowto be incorrect. Furthermore, it appears that the counts are beingmultiplied together (in one instance, enrolled should be 14, waitingshould be 2, but both numbers come back as 28).
If I run this query without one of the joined tables, the counts areaccurate. The problem only occurs when I try to pull counts from boththe tables.
Can anyone find the problem with my query? Should I be using something other than a LEFT OUTER JOIN?
Thanks very much for your time,
--Jeremy
Run this query and you'll see what it's doing:
SELECT Class.Name, Enrolled.StudentID AS EnrolledCount, Waiting.StudentID AS WaitingCount
FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID
Something like this will work:
SELECT c.Name, e.EnrolledCount, w.WaitingCount
FROM Class c
LEFT OUTER JOIN
(select classid, COUNT(*) AS EnrolledCount
from Enrolled
group by classid) e
on c.classid = e.classid
LEFT OUTER JOIN
(select classid, COUNT(*) AS WaitingCount
from Waiting
group by classid) w
on c.classid = w.classid
There's other ways to do it with subqueries. Something like this would also work
select c.classname, (select count(*) from enrolled where classid = c.classid) as Enrolled, (select count(*) from waiting where classid = c.classid) as Waiting
from Class c|||Thanks, PDraigh. I used your subquery example and it worked great.
Thanks!
--Jeremy