TABLE1
TEACHER DATE1 DATE2
1 2018-04-28 2019-05-30
1 2019-09-03 2019-09-06
1 2019-10-13 2019-12-21
2 2018-01-10 2018-12-30
2 2019-09-18 2019-11-03
3 2018-01-04 2019-07-27
4 2018-05-08 2019-01-19
4 2019-11-09 2019-11-20
TABLE2
STUDENT TEACHER DATE
1 2 2019-08-09
1 2 2018-12-16
1 4 2018-11-01
1 2 2018-10-31
1 4 2019-09-05
2 3 2019-08-13
2 3 2019-10-19
2 4 2018-09-16
2 1 2018-06-12
2 3 2019-03-02
2 4 2018-12-19
2 4 2019-04-24
2 4 2018-09-16
I have TABLE1 and TABLE2. I wish to join TABLE1 and TABLE2 to make table WANT
WANT
STUDENT TEACHER DATE
1 2 2018-12-16
1 2 2018-10-31
1 4 2018-11-01
2 1 2018-06-12
2 3 2019-03-02
2 4 2018-12-19
2 4 2019-04-24
Basically I wish to include in WANT only rows where the DATE is between DATE1 and DATE2 for every TEACHER in TABLE1.
For example in TABLE 2 there is this row
1 2 2019-08-09
that does not come into table WANT because the DATE value 2019-08-09 is not in between the VALID DATES for TEACHER=2 shown in TABLE1.
I try this without success
SELECT * FROM TABLE2
WHERE
TEACHER IN (SELECT TEACHER FROM TABLE1)
AND DATE >= (SELECT DATE1 FROM TABLE1)
AND DATE <= (SELECT DATE2 FROM TABLE1)
Since you are only after qualifying rows from table2, the optimal way to write this would be to use a semi-join with exists:
select *
from t2
where exists (
select * from t1
where t1.teacher = t2.teacher
and t2.date >= t1.date1
and t2.date <= t1.date2
);