Skip to content Skip to sidebar Skip to footer

Problem Constructing Where Clause To Include Null/0 Values

I have an application where users can take tests (which are composed of questions and answers). I'm trying to construct a query that returns a count of answers grouped by question,

Solution 1:

Move the teacher_students.teacher_id = 1 check from the WHERE clause to the joining ON clause.

When a condition that refers to a table in the right side of a LEFT JOIN is put in the WHERE clause, the LEFT JOIN is cancelled and it acts as an INNER JOIN.

SELECT count(teacher_students.student_id) AS rcount      <--- changed
     , questions.id 
FROM"questions"
  LEFT JOIN answers 
    ON answers.question_id = questions.id
  LEFT JOIN teacher_students 
    ON teacher_students.student_id = answers.student_id
    AND teacher_students.teacher_id = 1WHERE questions.test_id = 1GROUPBY questions.id 
ORDERBY questions.id

Post a Comment for "Problem Constructing Where Clause To Include Null/0 Values"