提问者:小点点

MYSQL Joins没有返回所需的记录


我需要一个查询,将检索请假请求加入两个表,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只返回已批准的叶子。

谢谢


共2个答案

匿名用户

使用左联接

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;