我需要一个查询,将检索请假请求加入两个表,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;