Left Join equals ID and between two dates

[mrmo]

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.


Here is a sample table set up below:


Table 1:
[TABLE="width: 377"]
<tbody>[TR]
[TD]EmployeeID
[/TD]
[TD]EmployeeName
[/TD]
[TD]RowDate
[/TD]
[TD]Metric
[/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]John Johnson
[/TD]
[TD]01/01/2019
[/TD]
[TD]125
[/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]John Johnson
[/TD]
[TD]01/02/2019
[/TD]
[TD]105
[/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]John Johnson
[/TD]
[TD]01/03/2019
[/TD]
[TD]180
[/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]John Johnson
[/TD]
[TD]01/04/2019
[/TD]
[TD]111
[/TD]
[/TR]
[TR]
[TD]2222
[/TD]
[TD]Mike Montgomery
[/TD]
[TD]01/01/2019
[/TD]
[TD]255
[/TD]
[/TR]
[TR]
[TD]2222
[/TD]
[TD]Mike Montgomery
[/TD]
[TD]01/02/2019
[/TD]
[TD]212
[/TD]
[/TR]
[TR]
[TD]2222
[/TD]
[TD]Mike Montgomery
[/TD]
[TD]01/03/2019
[/TD]
[TD]280
[/TD]
[/TR]
[TR]
[TD]2222
[/TD]
[TD]Mike Montgomery
[/TD]
[TD]01/04/2019
[/TD]
[TD]222
[/TD]
[/TR]
[TR]
[TD]3333
[/TD]
[TD]Sarah Sikes
[/TD]
[TD]01/01/2019
[/TD]
[TD]358
[/TD]
[/TR]
[TR]
[TD]3333
[/TD]
[TD]Sarah Sikes
[/TD]
[TD]01/02/2019
[/TD]
[TD]356
[/TD]
[/TR]
[TR]
[TD]3333
[/TD]
[TD]Sarah Sikes
[/TD]
[TD]01/03/2019
[/TD]
[TD]324
[/TD]
[/TR]
[TR]
[TD]3333
[/TD]
[TD]Sarah Sikes
[/TD]
[TD]01/04/2019
[/TD]
[TD]385
[/TD]
[/TR]
[TR]
[TD]4444
[/TD]
[TD]Willie Wilson
[/TD]
[TD]01/01/2019
[/TD]
[TD]435
[/TD]
[/TR]
[TR]
[TD]4444
[/TD]
[TD]Willie Wilson
[/TD]
[TD]01/02/2019
[/TD]
[TD]483
[/TD]
[/TR]
[TR]
[TD]4444
[/TD]
[TD]Willie Wilson
[/TD]
[TD]01/03/2019
[/TD]
[TD]412
[/TD]
[/TR]
[TR]
[TD]4444
[/TD]
[TD]Willie Wilson
[/TD]
[TD]01/04/2019
[/TD]
[TD]483
[/TD]
[/TR]
</tbody>[/TABLE]



Table 2:
[TABLE="width: 305"]
<tbody>[TR]
[TD]EmployeeID
[/TD]
[TD]Supervisor
[/TD]
[TD]StartDate
[/TD]
[TD]EndDate
[/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]Sup 1
[/TD]
[TD]01/08/2017
[/TD]
[TD]11/19/2017
[/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]Sup 2
[/TD]
[TD]11/20/2017
[/TD]
[TD]12/01/2018
[/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]Sup 3
[/TD]
[TD]12/02/2018
[/TD]
[TD]12/31/9999
[/TD]
[/TR]
[TR]
[TD]2222
[/TD]
[TD]Sup 4
[/TD]
[TD]01/12/2018
[/TD]
[TD]12/05/2018
[/TD]
[/TR]
[TR]
[TD]2222
[/TD]
[TD]Sup 5
[/TD]
[TD]12/06/2018
[/TD]
[TD]12/31/2018
[/TD]
[/TR]
[TR]
[TD]3333
[/TD]
[TD]Sup 6
[/TD]
[TD]01/01/2015
[/TD]
[TD]12/31/9999
[/TD]
[/TR]
</tbody>[/TABLE]



Expected Query Result:
[TABLE="width: 486"]
<tbody>[TR]
[TD]EmployeeID
[/TD]
[TD]EmployeeName
[/TD]
[TD]RowDate
[/TD]
[TD]Metric
[/TD]
[TD]Supervisor
[/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]John Johnson
[/TD]
[TD]01/01/2019
[/TD]
[TD]125
[/TD]
[TD]Sup 3
[/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]John Johnson
[/TD]
[TD]01/02/2019
[/TD]
[TD]105
[/TD]
[TD]Sup 3
[/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]John Johnson
[/TD]
[TD]01/03/2019
[/TD]
[TD]180
[/TD]
[TD]Sup 3
[/TD]
[/TR]
[TR]
[TD]1111
[/TD]
[TD]John Johnson
[/TD]
[TD]01/04/2019
[/TD]
[TD]111
[/TD]
[TD]Sup 3
[/TD]
[/TR]
[TR]
[TD]2222
[/TD]
[TD]Mike Montgomery
[/TD]
[TD]01/01/2019
[/TD]
[TD]255
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2222
[/TD]
[TD]Mike Montgomery
[/TD]
[TD]01/02/2019
[/TD]
[TD]212
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2222
[/TD]
[TD]Mike Montgomery
[/TD]
[TD]01/03/2019
[/TD]
[TD]280
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2222
[/TD]
[TD]Mike Montgomery
[/TD]
[TD]01/04/2019
[/TD]
[TD]222
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3333
[/TD]
[TD]Sarah Sikes
[/TD]
[TD]01/01/2019
[/TD]
[TD]358
[/TD]
[TD]Sup 6
[/TD]
[/TR]
[TR]
[TD]3333
[/TD]
[TD]Sarah Sikes
[/TD]
[TD]01/02/2019
[/TD]
[TD]356
[/TD]
[TD]Sup 6
[/TD]
[/TR]
[TR]
[TD]3333
[/TD]
[TD]Sarah Sikes
[/TD]
[TD]01/03/2019
[/TD]
[TD]324
[/TD]
[TD]Sup 6
[/TD]
[/TR]
[TR]
[TD]3333
[/TD]
[TD]Sarah Sikes
[/TD]
[TD]01/04/2019
[/TD]
[TD]385
[/TD]
[TD]Sup 6
[/TD]
[/TR]
[TR]
[TD]4444
[/TD]
[TD]Willie Wilson
[/TD]
[TD]01/01/2019
[/TD]
[TD]435
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4444
[/TD]
[TD]Willie Wilson
[/TD]
[TD]01/02/2019
[/TD]
[TD]483
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4444
[/TD]
[TD]Willie Wilson
[/TD]
[TD]01/03/2019
[/TD]
[TD]412
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4444
[/TD]
[TD]Willie Wilson
[/TD]
[TD]01/04/2019
[/TD]
[TD]483
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





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!

 

[mrmo]

I found the answer. Am I allowed to answer my own question? Oh well, if so, here's the answer.

SELECT Table1.EmployeeID, Table1.EmployeeName, Table1.RowDate, Table1.Metric, Query1.Supervisor
FROM Table1 LEFT JOIN (SELECT Table1.EmployeeID, Table1.EmployeeName, Table1.RowDate, Table1.Metric, Table2.Supervisor FROM Table1 INNER JOIN Table2 ON Table1.EmployeeID = Table2.EmployeeID WHERE (((Table2.StartDate)<=[RowDate]) AND ((Table2.EndDate)>=[RowDate]))) AS Query1 ON (Table1.EmployeeID = Query1.EmployeeID) AND (Table1.RowDate = Query1.RowDate)

 

[Joe4]

I found the answer. Am I allowed to answer my own question?

Certainly. We actually encourage (and appreciate) it when you do!