SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
Hybrid View
-
Dec 31, 2004, 07:24 #1
- Join Date
- Oct 2004
- Location
- Sutton, Surrey
- Posts
- 259
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
How to count() from multiple tables
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?
-
Dec 31, 2004, 08:05 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
there are several ways to do it, but you'll need to be on mysql 4.1
Code: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
Code: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
-
Dec 31, 2004, 12:01 #3
- Join Date
- Oct 2004
- Location
- Sutton, Surrey
- Posts
- 259
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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'.
-
Dec 31, 2004, 12:15 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
okay, then, just stick with the first one
Bookmarks