Join table on date column that falls between start and end date in second table-CodePudding

I have two tables - #teacher and #kids

I need to match a kid with his/her teacher depending on who was working that day.

Desired output:

Code for tables:

CREATE TABLE #teacher
(
    teacher_name varchar(20),
    start_date date,
    end_date date
)

CREATE TABLE #kids
(
    child_name varchar(20),
    attendance date,
)

INSERT INTO #teacher
    SELECT 'Mary', '2017/01/01', '2017/10/10'
    UNION
    SELECT 'Jane', '2020/05/15', '2021/09/20'
    UNION
    SELECT 'Jenny', '2021/10/25', '2022/02/20'
    
    
INSERT INTO #kids
    SELECT 'Tommy', '2021/11/10'
    UNION
    SELECT 'Sadie', '2017/06/23'
    UNION
    SELECT 'Arthur', '2021/12/25'

CodePudding user response：

Just use a join. The join condition is exactly what you wrote in question title:

select k.child_name, k.attendance, t.teacher_name
from #kids k
left join #teachers t on k.attendance between t.start_date and t.end_date