MYSQL显示所有记录并统计相关记录

Question

我有 3 个表，老师的科目和作业。当老师登录时，我想显示所有科目，无论他是否是老师，无论是否有相关记录。如果没有相关记录(作业)，我希望它显示 0。

Teacher Table: Teacher_PK, Teacher_name    
Subject Table:  Subject_PK, Teacher_fk, Subject_name, 
Assignments Table: Assignment_PK, Subject_fk, assignment_name

现在我的情况是这样的:

"SELECT *, count(a.subject_fk)
        FROM assignment AS b
        LEFT OUTER JOIN subject AS a
        ON a.subject_fk = b.subject_PK
        WHERE a.teacher_fk = $sessionVar
        GROUP BY b.subject_fk
        ORDER BY b.subject_name ASC";

此查询的问题是它不显示所有主题，仅在存在相关表时显示主题。

English (3)
Math(2)

我想要它显示的是

English(3)
Math(2)
Gym(0)
Science(0)

感谢您的帮助。主题表中的所有主题

Answer 1

SELECT
  s.*,
  count(a.assignment_pk) as numAssignments
FROM
  Subject s
   LEFT OUTER JOIN Assignments a ON
    s.Subject_pk = a.Subject_fk AND
    a.teacher_fk = $sessionVar
GROUP BY 
  s.*
ORDER BY
  s.subject_name ASC

编辑 - 将教师 ID 上的过滤器移至外部连接部分