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?