Not getting data when trying to create multiple inner join on 2 columns from the same table

I have a table and data like below:

Employee:

Id      DepartmentId
1       100
2       100
3       100

LeaveRequest:

Id    SentFromEmployeeId    SentToEmployeeId
1     1                     2
2     1                     2
3     2                     3

LeaveUpdateLogs:

Id      RequestedDate             LeaveRequestId     Status
1       2021-11-01 11:55:56       1                  Pending
2       2021-11-02 10:55:56       1                  Accepted
3       2021-11-03 11:55:56       1                  Accepted
4       2021-11-04 09:55:56       1                  Declined
5       2021-11-05 10:55:56       1                  Closed

6       2021-11-06 05:55:56       2                  Pending
7       2021-11-07 05:55:56       2                  Accepted
8       2021-11-08 02:55:56       2                  Accepted
9       2021-11-09 05:55:56       2                  Declined
10       2021-11-10 05:55:56       2                  Closed

Now here I want to calculate statistics as below for a particular department:

Total number of requests sent and received for DepartmentId 100.

But I am confused here for getting data for “Sent” and “Received” like below:

select SentFromEmployeeId,SentToEmployeeId,* 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)

But this doesn’t return any data although I have “25” records between this 2 dates. When I comment out this 3 lines:

inner join Employee e1 on e1.Id = lr.SentFromEmployeeId
inner join Employee e2 on e2.Id = lr.SentToEmployeeId
(e1.DepartmentId =100 or e2.DepartmentId = 100)

Then query is working fine but I want to get the data for particular department.

Can someone please help me fix this logic?

Answer

Since I think you are only asking how to join on multiple values here are a couple ways of you could tackle this.

inner join Employee e1 on e1.Id in (lr.SentFromEmployeeId, lr.SentToEmployeeId)

inner join Employee e1 on e1.Id = lr.SentFromEmployeeId 
                       OR e1.Id = lr.SentToEmployeeId

Advertisement

Answer