MySQL Left Join Count Not Working

Question

I have 2 tables (members, duty_sched) I want to be able to count how many times each member appears in the duty_sched table. This is what I have after several weeks of 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 result I get is all zeros that I would like to show:

Change of name Bob Smith 4 Jane Johnson 2

Any help I can get would be greatly appreciated.

Answer 1

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

You can also write your query using 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 ; 

Answer 2

You can try something like this

 SELECT members.fname, members.lname, count(*) FROM duty_sched JOIN members WHERE duty_sched.mbr_id = members.mbr_id Group by duty_sched.mbr_id ORDER BY members.lname, members.fname 

Answer 3

Try the following:

 SELECT m.fname, m.lname, IFNULL(A.shifts, 0) shifts FROM members m LEFT JOIN (SELECT mbr_id, COUNT(id) shifts FROM duty_sched GROUP BY mbr_id) A ON m.mbr_id = A.mbr_id ORDER BY m.lname, m.fname;