我有两个表要加入,到目前为止,我有一个LEFT JOIN工作正常,但我希望它更具体。
现在的情况是:

SELECT m.fname, m.lname, m.position, COUNT(d.mId) AS shifts
FROM members AS m
LEFT JOIN duty_sched AS d
ON d.mId = m.id AND month(d.shift_date) = $month
WHERE m.active_mbr = '1' AND m.fname != 'memorial'
GROUP BY m.id
ORDER BY m.lname, m.fname

在d中,我有一个名为status的列,我想在混合中也有这个列。列表必须包含duty_sched中具有班次计数的成员的所有人员。
例子:
Name (Count)

John  (1)
Mary  (0)
Mark  (2)

最佳答案

尝试:

SELECT m.fname, m.lname, m.position, COUNT(d.mId) AS shifts FROM
members as m
LEFT JOIN duty_sched AS d
ON d.mId = m.id AND month(d.shift_date) = $month
WHERE m.active_mbr = '1' AND m.fname != 'memorial'
GROUP BY m.id
HAVING COUNT(d.mId)>0
ORDER BY m.lname, m.fname

07-24 09:51
查看更多