LEFT JOIN equals ID and between two dates

[mrmo1981]

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!

 

[June7]

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);

 

[mrmo1981]

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]

You read my post too quickly! Look at it again.

 

[mrmo1981]

Exactly what I was looking for. Thank you so much!