SQL对同表两列做多次inner join查询无返回数据如何解决？

问题原因 #

1. 字段歧义导致执行异常：你在SELECT子句中直接写SentFromEmployeeId、SentToEmployeeId以及*，关联两张Employee表后存在大量重名字段（两张Employee表都有Id、DepartmentId字段），多数数据库遇到无法解析的重名字段时会执行报错或返回空结果，这是你加了Employee关联后无数据的核心原因。
2. 统计逻辑偏差：LeaveUpdateLogs表中1条请假申请对应多条更新日志，如果你要统计的是请假申请的数量而非日志条数，直接关联会出现重复计数的问题。

修复方案 #

1. 查看明细数据的修复SQL #

明确指定所有字段的所属表，避免重名冲突：

SELECT 
    lr.SentFromEmployeeId,
    lr.SentToEmployeeId,
    l.Id AS log_id,
    l.RequestedDate,
    l.Status,
    lr.Id AS request_id,
    e1.DepartmentId AS send_department_id,
    e2.DepartmentId AS receive_department_id
FROM LeaveUpdateLogs l
INNER JOIN LeaveRequest lr ON l.LeaveRequestId = lr.Id
INNER JOIN Employee e1 ON e1.Id = lr.SentFromEmployeeId
INNER JOIN Employee e2 ON e2.Id = lr.SentToEmployeeId
WHERE l.RequestedDate >= '2021-11-01' 
  AND l.RequestedDate < '2021-11-16'
  AND (e1.DepartmentId = 100 OR e2.DepartmentId = 100)

2. 统计请假申请总数的SQL #

如果需要统计去重后的请假申请数量，以及分开统计发出、收到的数量：

SELECT 
    COUNT(DISTINCT lr.Id) AS total_request_count,
    COUNT(DISTINCT CASE WHEN e1.DepartmentId = 100 THEN lr.Id END) AS send_request_count,
    COUNT(DISTINCT CASE WHEN e2.DepartmentId = 100 THEN lr.Id END) AS receive_request_count
FROM LeaveUpdateLogs l
INNER JOIN LeaveRequest lr ON l.LeaveRequestId = lr.Id
INNER JOIN Employee e1 ON e1.Id = lr.SentFromEmployeeId
INNER JOIN Employee e2 ON e2.Id = lr.SentToEmployeeId
WHERE l.RequestedDate >= '2021-11-01' 
  AND l.RequestedDate < '2021-11-16'
  AND (e1.DepartmentId = 100 OR e2.DepartmentId = 100)

特殊场景适配 #

如果实际业务中存在请假申请人/审批人不在Employee表的情况，可将两次INNER JOIN改为LEFT JOIN，避免有效数据被过滤。

内容的提问来源于stack exchange，提问作者I Love Stackoverflow