mysql - How to write a query with count -

i have 2 tables follows:

================== studentsclasses   ---------------- id (registration id of class) studentid (id of student taking class) classid (id of class) ---------------- ==================  students --------------- id (id of student) name (name of student) gradelevelid (grade of student) --------------- ================== 

and joined studentsclasses.studentid , students.id.

i trying write query return students least classes registered. query is:

select students.name, count(studentsclasses.studentid) expr1     studentsclasses inner join                   students on studentsclasses.studentid = students.id group studentsclasses.studentid, students.name order expr1 

however, returns students @ least 1 class in asc order.

i know correct answer 7 students 0 classes.

how can modify query return 7 students 0 classes.

to enlist students, have no classes, instead of inner join should using left join here, make sure rows students table listed, though there no rows in studentclasses particular student.

select  s.name, count(sc.id) classes  students s  left join studentsclasses sc on s.id = sc.studentid group s.name having count(sc.id) = 0 -- added after comment order count(sc.id); 

or method (for retrieving students have 0 classes):

select  s.name  students.s  left join studentsclasses sc on s.id = sc.studentid  sc.id null