SELECT CaseFolder.CaseId, CaseFolder.CaseCoverImageFileName,
CaseFolder.CaseName, count(Event.CaseId), CaseFolder.CaseColor,
CaseFolder.IsMedicalCase, CaseFolder.StartDate, CaseFolder.EndDate
FROM CaseFolder
LEFT JOIN Event ON CaseFolder.CaseId = Event.CaseId
WHERE Event.PersonId = 1 AND Event.IsDeleted = 0 AND CaseFolder.IsDeleted = 0
GROUP BY CaseFolder.CaseId
我通过表Event
和表CaseFolder
选择Count
, primary key
是CaseId
,但是我需要选择包含在表中但不包含在另一个表中的数据,当必须添加"where"时,"LEFT ON"。如果我删除sql代码WHERE Event.PersonId = 1 AND Event.IsDeleted = 0 AND CaseFolder.IsDeleted = 0
的结果是正确的。因为WHERE
包含不适合CaseFolder
表的条件,我该怎么办?
left join
中第二个表的条件应该放在on
子句中:
SELECT CaseFolder.CaseId, CaseFolder.CaseCoverImageFileName,
CaseFolder.CaseName, count(Event.CaseId), CaseFolder.CaseColor,
CaseFolder.IsMedicalCase, CaseFolder.StartDate, CaseFolder.EndDate
FROM CaseFolder LEFT JOIN
Event
ON CaseFolder.CaseId = Event.CaseId AND
Event.PersonId = 1 AND Event.IsDeleted = 0
WHERE CaseFolder.IsDeleted = 0;
GROUP BY CaseFolder.CaseId ;
否则,LEFT JOIN
将变为INNER JOIN
,因为条件失败,NULL
的值。注意,使用表别名编写和读取查询更容易:
SELECT cf.CaseId, cf.CaseCoverImageFileName,
cf.CaseName, count(e.CaseId), cf.CaseColor,
cf.IsMedicalCase, cf.StartDate, cf.EndDate
FROM CaseFolder cf LEFT JOIN
Event e
ON cf.CaseId = e.CaseId AND
e.PersonId = 1 AND e.IsDeleted = 0
WHERE cf.IsDeleted = 0;
GROUP BY cf.CaseId ;