我需要一个查询，将检索请假请求加入两个表，staff和； LeaveRequest。 可以批准或请求该请求。 有由(&； 标识审批者employeeid(&P； 请求者，其记录可以在staff表中作为employeeID找到。 如果请求未被批准，approvedby记录将为空。
SELECT s.letterRequestId
, s.businessId letterRequestType
, r.approvedBy
, DATE(s.approvedDate) approvedDate
, s.requestedBy
, DATE(s.requestedDate) requestedDate
, s.employeeId
, r.status
, r.documentPath
, CONCAT(s.firstName, " ", s.lastName ) Name
, CONCAT(a.firstName, " ", a.lastName ) `Approver Name`
FROM Leave_Requests r
join Employee s
on e.employeeId = r.employeeId
join Employee a
on a.employeeId = r.approvedBy;
上述sql只返回已批准的叶子。
谢谢
使用左联接
SELECT s.letterRequestId
, s.businessId letterRequestType
, r.approvedBy
, DATE(s.approvedDate) approvedDate
, s.requestedBy
, DATE(s.requestedDate) requestedDate
, s.employeeId
, r.status
, r.documentPath
, CONCAT(s.firstName, " ", s.lastName ) Name
, CONCAT(a.firstName, " ", a.lastName ) `Approver Name`
FROM Leave_Requests r
LEFT join Employee s
on e.employeeId = r.employeeId
LEFT join Employee a
on a.employeeId = r.approvedBy;
您具有与approver表的隐式内部联接（在requests.approvedby上联接），该表筛选出在approvedby上具有null值的行。
您应该将其替换为左联接，并在连接审批者名称时正确处理null值:
SELECT s.letterRequestId
, s.businessId letterRequestType
, r.approvedBy
, DATE(s.approvedDate) approvedDate
, s.requestedBy
, DATE(s.requestedDate) requestedDate
, s.employeeId
, r.status
, r.documentPath
, CONCAT(s.firstName, " ", s.lastName ) Name
, CONCAT(a.firstName, " ", a.lastName ) `Approver Name`
FROM Leave_Requests r
join Employee s
on e.employeeId = r.employeeId
LEFT JOIN Employee a
on a.employeeId = r.approvedBy;
