MySQL 左连接计数不工作

Question

我有 2 个表(成员，duty_sched)我希望能够计算每个成员出现在 duty_sched 表中的次数。这是我经过数周搜索后得到的

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

我得到的输出全为零，我希望它向我展示的是:

名称转换鲍勃·史密斯 4简·约翰逊 2

我将不胜感激任何帮助。

Answer 1

删除单引号:WHERE duty_sched.mbr_id = members.mbr_id)

您还可以使用 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 ;