Retrieve records from left table based on matching with right table and also non matching records fr-CodePudding

We have the requirements as per following, please guide me on how to achieve this.

Study table

id      startedDate             practitioner        reference       laterality
-------------------------------------------------------------------------------
1s      2022-09-01 00:00:00     p1                  1-reference      R
2s      2022-10-01 00:00:00     p1                  1-reference      L
3s      2022-09-03 00:00:00     null                2-reference      R
4s      2022-09-03 00:00:00     null                3-reference      R
5s      2022-10-03 00:00:00     p1                  4-reference      L

Obs Table

id          reference       laterality          effectiveDate         length
-------------------------------------------------------------------------------
1o         1-reference         R                2022-08-30 00:00:00    2.1
2o         1-reference         R                2022-08-01 00:00:00    2.2
3o         1-reference         R                2022-09-03 00:00:00    2.1
4o         1-reference         L                2022-08-03 00:00:00    2.0
5o         2-reference         R                2022-08-03 00:00:00    2.0
6o         2-reference         R                2022-08-01 00:00:00    2.0
7o         3-reference         R                2022-10-01 00:00:00    2.0
8o         5-reference         L                2022-10-02 00:00:00    1.9
90         5-reference         L                2022-10-03 00:00:00    2.0

Output:

get max effectiveDate record group by reference and laterality but less than study.startedDate if practitioner!=null and latest effectiveDate record group by reference and laterality if that record not found in study table on practitioner!=null

result:

id         reference          laterality        effectiveDate          length
1o         1-reference         R                2022-08-30 00:00:00    2.1
4o         1-reference         L                2022-08-03 00:00:00    2.0
5o         2-reference         R                2022-08-03 00:00:00    2.0
7o         3-reference         R                2022-10-01 00:00:00    2.0
9o         5-reference         L                2022-10-03 00:00:00    2.0

the below example query only get matched records or understanding purpose but final output will be as above

select Obs.*
from Obs
inner join study
on Obs.reference = study.reference and Obs.effectiveDate < study.startedDate
and study.practitioner != null and Obs.laterality = study.laterality

Can someone please help me to achieve this.

Thanks

CodePudding user response：

From your description, it seems like this is what you wanted.

You can use row_number() to get the row with latest effectiveDate

select *
from
(
    select Obs.*,
           rn = row_number() over (partition by Obs.reference, Obs.laterality
                                   order by Obs.effectiveDate desc)
    from   Obs
           inner join study  on Obs.reference  = study.reference 
                            and Obs.laterality = study.laterality
    where  (study.practitioner is not null and  Obs.effectiveDate < study.startedDate)
    or     (study.practitioner is null)
) o
where o.rn = 1
order by Id