SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    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'.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, then, just stick with the first one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •