How to count() from multiple tables

[Tony Marston]

I have 3 tables as follows:

Lesson:

lesson_id | lesson_name |
-------------------------|
1 | Lesson One |
2 | Lesson Two |
3 | Lesson Three |
4 | Lesson Four |
--------------------------

student_lesson:

lesson_id | student_id |
----------------------|
1 | 44 |
1 | 45 |
1 | 46 |
1 | 47 |
----------------------|

class_lesson:

lesson_id | class_id |
-------------------|
1 | 901 |
2 | 902 |
3 | 903 |
4 | 904 |
-------------------|

The following query:

SELECT crs_lesson.lesson_id, lesson_name,
count(crs_student_lesson.student_id) AS student_count,
count(crs_class_lesson.class_id) AS class_count
FROM crs_lesson
LEFT JOIN crs_student_lesson USING (lesson_id)
LEFT JOIN crs_class_lesson USING (lesson_id)
GROUP BY crs_lesson.lesson_id

Produces the following result:

lesson_id | lesson_name | student_count | class_count |
------------------------------------------------------|
1 | Lesson One | 4 | 4 |
2 | Lesson Two | 0 | 0 |
3 | Lesson Three | 0 | 0 |
4 | Lesson Four | 0 | 0 |
------------------------------------------------------|

This shows that the second count is always set to the same value as the first count even though the values should be different. Does anyone know how to obtain the correct values?

[r937]

there are several ways to do it, but you'll need to be on mysql 4.1

select L.lesson_id
     , L.lesson_name
     , ( select count(student_id)
           from crs_student_lesson 
          where lesson_id = L.lesson_id ) as student_count
     , ( select count(class_id)
           from crs_class_lesson 
          where lesson_id = L.lesson_id ) as class_count 
  from crs_lesson as L
group 
    by L.lesson_id 
     , L.lesson_name

select L.lesson_id
     , L.lesson_name
     , student_count
     , class_count 
  from crs_lesson as L
left outer
  join ( select count(student_id) as student_count
           from crs_student_lesson ) as SL
    on L.lesson_id = SL.lesson_id 
left outer
  join ( select count(class_id) as class_count
           from crs_class_lesson ) as CL
    on L.lesson_id = CL.lesson_id 
group 
    by L.lesson_id 
     , L.lesson_name

[Tony Marston]

Thanks very much! I got the first piece of code working OK, but the second one fails with - Unkown column 'SL.lesson_id' in 'on clause'.

[r937]

okay, then, just stick with the first one