MySQL Left Join Count Not working

Question

I have 2 tables (members, duty_sched) I want to be able to count how many time each member appears in the duty_sched table. This is what I have after several weeks searching

SELECT 
members.fname, 
members.lname, 
(SELECT COUNT(duty_sched.id) FROM duty_sched
 WHERE 'duty_sched.mbr_id' = 'members.mbr_id') AS shifts 

FROM members
ORDER BY members.lname, members.fname

The output I'm getting is all zeros, what I would like it to show me is:

Name shifts Bob Smith 4 Jane Johnson 2

Any help I can get will be greatly appreciated.

Answer 1

Remove the single quotes: WHERE duty_sched.mbr_id = members.mbr_id)

You can also write your query with a LEFT JOIN:

SELECT 
    m.fname, 
    m.lname, 
    COUNT(d.id) AS shifts 
FROM members AS m
  LEFT JOIN duty_sched AS d
    ON d.mbr_id = m.mbr_id
GROUP BY
    m.mbr_id                     -- the Primary Key of `members`
ORDER BY 
    m.lname, m.fname ;