LEFT JOIN equals ID and between two dates (1 Viewer)

mrmo1981

New member
Local time
Today, 15:13
Joined
Apr 18, 2019
Messages
3
Hi all,

Hoping someone can help me with query I'm having trouble with. I want to do a left join and keep all records in Table 1, and matching records in Table 2 where Table 1 ID = Table 2 ID, AND Table 1 RowDate is between Table 2 StartDate and EndDate.

I'm going to try to attach a sample DB, but here is my table set up below in case it doesn't work:


Table 1 columns:
EmployeeID
EmployeeName
RowDate
Metric

Table 2 columns:
EmployeeID
Supervisor
StartDate
EndDate

Expected Query Result:
EmployeeID
EmployeeName
RowDate
Metric
Supervisor




In the expected query result, I want all rows from Table 1. Then the Supervisor value can be null when it can't find the EmployeeID at all, or when it can't find the RowDate between Start and End dates for an employee in Table 2.

Thanks for looking!
 
Last edited:

June7

AWF VIP
Local time
Today, 12:13
Joined
Mar 9, 2014
Messages
5,423
SELECT Table1.EmployeeID, Table1.EmployeeName, Table1.RowDate, Table1.Metric, Table2.Supervisor
FROM Table1 LEFT JOIN Table2 ON Table1.EmployeeID = Table2.EmployeeID
WHERE [RowDate] BETWEEN Table2.StartDate AND Table2.EndDate;

Your query and this version can return only records from Table1 that meet the criteria. If there is no record in Table 2 then there is no date range to match and record cannot return. So use the above in another query:

SELECT Table1.EmployeeID, Table1.EmployeeName, Table1.RowDate, Table1.Metric, Query1.Supervisor
FROM Query1 RIGHT JOIN Table1 ON (Query1.RowDate = Table1.RowDate) AND (Query1.EmployeeID = Table1.EmployeeID);
 
Last edited:

mrmo1981

New member
Local time
Today, 15:13
Joined
Apr 18, 2019
Messages
3
HI there, thanks for the quick reply.
Your query only returns the Johnson and Sikes records.

I would like it to return ALL records from Table 1,
and a Null in the Supervisor column when it can't find a match.
 

June7

AWF VIP
Local time
Today, 12:13
Joined
Mar 9, 2014
Messages
5,423
You read my post too quickly!;) Look at it again.
 

Users who are viewing this thread

Top Bottom