Mysql count with multiple left join

Question

I'm really having trouble with mysql query to retrieve records with counts. Here's the scenario. I would like to get master table (criteria) rows with students count who are not present in selected_students table.

criteria
---------
id  name
1   maths
2   medicine
3   physics

criteria_matches
------------------
id  criteria_ref_id   user_ref_id
1          1            153
2          1            222
3          1            699
4          2            486
5          2            38
6          3            141

selected_students
-----------------
id  user_ref_id  selected_by  status
1        153        15     'E'
2        500        15     'E'
3        486        15     'E'
3        141        15     'E'



expected result
----------------
criteria_id criteria_name   student_count
1               maths       2
2               medicine    1
3               physics     0

I tried with the below query to get the parent table rows with counts but no luck

SELECT t.id, t.name, COUNT(DISTINCT ss.user_ref_id) AS students_count FROM criteria t  
LEFT JOIN criteria_matches cm ON cm.criteria_ref_id = t.id
LEFT JOIN selected_students ss ON ss.user_ref_id = cm.user_ref_id
WHERE dss.user_ref_id IS NULL
GROUP BY t.id ORDER BY t.name

Answer 1

You need to count user_ref_id from the criteria_matches table, not from selected_students (due to the filter in your WHERE clause*, the latter will always be NULL and therefore a COUNT() thereof will always be 0):

SELECT   t.id, t.name, COUNT(DISTINCT cm.user_ref_id) AS students_count
FROM     criteria t  
  LEFT JOIN criteria_matches  cm ON cm.criteria_ref_id = t.id
  LEFT JOIN selected_students ss ON ss.user_ref_id     = cm.user_ref_id
WHERE    ss.user_ref_id IS NULL
GROUP BY t.id
ORDER BY t.name

See it on sqlfiddle.

---

^{* NB: the WHERE clause in your question erroneously references the undefined table alias dss. I assume you meant to reference the ss alias of the selected_students table.}