left join on the first appeared row by datetime

If classes ‘Math’ or ‘Biology’ have flag is NULL or = 0, we need to left join with the first appeared (according to the date) classes ‘Literature’ or ‘English Literature’ from the below.

If classes ‘Math’ or ‘Biology’ have flag = 1 then we need to left join with the most recent (according to the date) classes ‘Literature’ or ‘English Literature’

Initial table:

| class              | date                    | flag |
| ------------------ | ----------------------- | ---- |
| Math               | 2020-07-07 20:08:00.000 | 0    |
| Biology            | 2020-07-07 21:08:00.000 | 1    |
| Math               | 2020-07-08 17:08:00.000 | NULL |
| English            | 2020-07-10 13:08:00.000 | 0    |
| Literature         | 2020-07-15 20:08:00.000 |      |
| English Literature | 2020-07-15 21:08:00.000 |      |
| Math               | 2020-07-15 22:08:00.000 | 1    |
| Literature         | 2020-09-09 20:08:00.000 |      |
| Math               | 2020-09-16 11:08:00.000 | 1    |
| English            | 2020-09-17 13:18:00.000 | 0    |
| Biology            | 2020-09-19 13:18:00.000 | NULL |

Result table (contain class without class = ‘Literature’, ‘English Literature’):

| class   | date                    | flag | class_2            | date_2                  |
| ------- | ----------------------- | ---- | ------------------ | ----------------------- |
| Math    | 2020-07-07 20:08:00.000 | 0    | Literature         | 2020-07-15 20:08:00.000 |
| Biology | 2020-07-07 21:08:00.000 | 1    |                    |                         |
| Math    | 2020-07-08 17:08:00.000 | NULL | Literature         | 2020-07-15 20:08:00.000 |
| English | 2020-07-10 13:08:00.000 | 0    |                    |                         |
| Math    | 2020-07-15 22:08:00.000 | 1    | English Literature | 2020-07-15 21:08:00.000 |
| Math    | 2020-09-16 11:08:00.000 | 1    | Literature         | 2020-09-09 20:08:00.000 |
| English | 2020-09-17 13:18:00.000 | 0    |                    |                         |
| Biology | 2020-09-19 13:18:00.000 | NULL |                    |                         |

Explanations:

1. Biology at 2020-09-19 13:18:00.000 doesn’t have a join because it has flag NULL and below (later than ‘Biology’) there are no rows with ‘Literature’ and ‘English Literature’ according to the rules.
2. Biology at 2020-07-07 21:08:00.000 with flag 1 also doesn’t have a join because there was no ‘ English Literature‘ or ‘ Literature‘ before

3) Math at 2020-07-07 20:08:00.000 with flag 0 (it is the first row) . As the flag is 0 so we look at the first appeared ‘Literature’ or ‘English Literature’ class after this class. The first appeared is ‘Literature’ at 2020-07-15 20:08:00.000 so we match them

How to do LEFT JOIN with finding recent or first row according to the date?

Answer

Without claiming that it is the most elegant solution; this would work:

with Tbl as (
    select Class, Date=cast(date as datetime), flag
    from (values
     ('Math',' 2020-07-07 20:08:00.000 ', 0    )
    ,('Biology',' 2020-07-07 21:08:00.000 ', 1    )
    ,('Math',' 2020-07-08 17:08:00.000 ', NULL )
    ,('English',' 2020-07-10 13:08:00.000 ', 0    )
    ,('Literature',' 2020-07-15 20:08:00.000 ',NULL      )
    ,('English Literature',' 2020-07-15 21:08:00.000 ',NULL      )
    ,('Math',' 2020-07-15 22:08:00.000 ', 1    )
    ,('Literature',' 2020-09-09 20:08:00.000 ',NULL      )
    ,('Math',' 2020-09-16 11:08:00.000 ', 1    )
    ,('English',' 2020-09-17 13:18:00.000 ', 0    )
    ,('Biology',' 2020-09-19 13:18:00.000 ', NULL )
    ) T(Class, date, flag)
),
T as (
 select 
       Tbl.*
     , Case when Class in ('Math','Biology') then 'MB' 
            when Class in ('Literature','English Literature') then 'LE'
            else 'Others'
       end as ClassGroup
 from Tbl
)
select *
from (select Ta.*
             , Tb.Class as B_Class
             , Tb.date as B_Date
             , Tb.flag as B_Flag
             , SeqAsc=row_number() over (partition by ta.ClassGroup, ta.Date order by tb.Date asc) 
             , SeqDesc=row_number() over (partition by ta.ClassGroup, ta.Date order by tb.Date desc) 
        from T as Ta
             left join
             T as Tb
             on Ta.ClassGroup='MB'
                and Tb.ClassGroup='LE'
                and ((coalesce(Ta.flag,0)=0 and Ta.date <= Tb.date) 
                     OR
                     (coalesce(Ta.flag,0)=1 and Ta.date >= Tb.date)
                    )
        where Ta.ClassGroup in ('MB','Others')
     ) T
where 
 (coalesce(T.flag,0)=0 and SeqAsc=1)
 or (coalesce(T.flag,0)=1 and SeqDesc=1)
 or B_Class is null
 order by Date, class

You have some blank flag values, I assumed NULL, You have not indicated which database products you used, I assumed SqlServer (but it should work with majority of them).