SQL query LEFT JOIN issue (MySQL)

Question

I have 2 tables

SCHOOLS (ID, SCHOOL_NAME, CITY_ID)
STUDENTS (ID, STUDENT_NAME, SCHOOL_ID).

I want to list schools in a particular city along with the student count (School Name| Student Count)

One way to do this is to have co-related subquery -

select sh.school_name, 
       (select count(*) 
          from student 
         where school_id = sh.id) as student_count 
 from schools sh 
where sh.city_id = 1

But since co-related queries are not recommended, I want to avoid that & I tried a group by -

   select sh.school_name, 
          count(st.school_id) as student_count 
     from schools sh 
left join students st on sh.id = st.school_id 
    where sh.city_id = 1 
 group by st.school_id

Now this works only if the student count for a school is > 0. So i m guessing the left join concept is not working, meaning if any schools that has no students should be listed as student_count=0, but that is not happening. The interesting thing is - i do see "one" record with student_count = 0, but thats about it.

Anything wrong in my LEFT JOIN query??

Answer 1

Use:

   SELECT sh.id,
          sh.school_name, 
          COUNT(st.school_id) as student_count 
     FROM SCHOOLS sh 
LEFT JOIN STUDENTS st on sh.id = st.school_id  
    WHERE sh.city_id = 1 
 GROUP BY sh.id, sh.school_name

Because of using the LEFT JOIN, references to st will be NULL if there are no students associated with the school. COUNT doesn't count NULL values, so if there are no students associated then student_count will be zero.

Answer 2

Try grouping by sh.id instead of st.school_id