CODEWITHSUNDEEP
mysqlcount
user982853
user982853

Reputation: 2488

MYSQL display all records and count related records

I have 3 tables teacher subject and assignments. When a teacher logs in, I want to display all the subjects regardless if he is the teacher and regardless if there is related records or not. If there is no related records (assignments) i want it to display 0. 

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

Right now i have it like this:

"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";

The problem with this query is that it does not show all subjects, it only shows the subject if there are related tables.

English (3)
Math(2)

What i want it to display is 

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

Thank you for any help. All subjects in the Subject table

Upvotes: 0

Views: 961

Answers (1)

Jake Feasel
Jake Feasel

Reputation: 16955

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

edit - moved the filter on teacher id to the outer join section

Upvotes: 1

Related Questions