By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table registered (
crn number,
term varchar2(6));
create table course(
crn number,
term varchar2(6),
term_descrip varchar2(25));
begin
insert into registered values (123,'202101');
insert into registered values (456,'202001');
insert into registered values (789,'202101');
insert into registered values (123,'202001');
insert into registered values (456,'201905');
insert into registered values (789,'202101');
insert into registered values (246,'202101');
insert into course values (123,'202001','Anatomy');
insert into course values (123,'202101','Physics');
insert into course values (456,'200001','English');
insert into course values (456,'201901','Algebra');
insert into course values (789,'199901','Gym');
end;
/
1 rows affected
select r.crn, r.term, c.term_descrip
from registered r
outer apply(
select *
from course c
where c.crn = r.crn
and c.term <= r.term
order by c.term desc
fetch first 1 rows only
) c
| CRN | TERM | TERM_DESCRIP |
|---|---|---|
| 123 | 202101 | Physics |
| 456 | 202001 | Algebra |
| 789 | 202101 | Gym |
| 123 | 202001 | Anatomy |
| 456 | 201905 | Algebra |
| 789 | 202101 | Gym |
| 246 | 202101 | null |