MySql, where clause filter off entries even with LEFT join : <p>3 tables: classes, students and class_student (association table). Following query output student count of each class. When there is no students in a class, the count(..) is 0 but class name is still in result.</p>

<pre><code>select c.name, count(s.id)
from classes c
    left join
  class_student cs on cs.classId = c.id
    left join
  students s on s.id = cs.studentId
group by c.id;
</code></pre>

<p>Now want to count students with score 100% of each class so a WHERE is added:</p>

<pre><code>select c.name, count(s.id)
from classes c
    left join
  class_student cs on cs.classId = c.id
    left join
  students s on s.id = cs.studentId
where s.score = 100  &lt;-- newly added
group by c.id;
</code></pre>

<p>then the classes that do not have any 100% students do not show up in the result. Considering LEFT JOIN is used, I'm expecting count(s.id) returns 0 when there is no 100% students. How can I make all classes are in result and those without any 100% student have count(..) output 0. Thanks</p>
