sqlleft-joinbetween

SQL Join and Check If In List


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)

Solution

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