t1
id | student_id | date |
t2
id | student_id | date |
select * from t1 t1 left join t2 t2 on t2.student_id = t1.student_id on t2.date = (select max(date) from t2 where date <= t1.date and student_id = t1.student_id group by student_id)
t1
id | student_id | date |
t2
id | student_id | date |
select * from t1 t1 left join t2 t2 on t2.student_id = t1.student_id on t2.date = (select max(date) from t2 where date <= t1.date and student_id = t1.student_id group by student_id)