CODEWITHSUNDEEP
sqlpostgresql
Alin-Iustinian Toderita
Alin-Iustinian Toderita

Reputation: 37

select MIN (Date) from another table in SQL

Given 2 tables of data I wanna extract min date from the second one in a third table using a query as follow:

Table 1

   id   value  date_of_payment  risk
    x1    100     01.01.2020     15X
    x1    50      01.05.2020     15Y

Table 2

id  start        end         risk
x1  01.01.2019   01.12.2021   15X
x1  01.05.2019   01.04.2022   15Y

Table 3 (result)

id   start(min)       end(max)          value   date_of_payment  risk
x1   01.01.2019       01.04.2022        100      01.01.2020      15X
x1   01.01.2019       01.04.2022        50       01.05.2020      15Y

I do not need to filter date by risk.

Please find below my code:

select p."Index", p."PAYVal", p."PAYDate"
from "DAMPay" as p
left join "PolsRisc" as pr on p."PRID" = pr."Index"
where p."PAYDate" between '2020-06-01' and '2020-06-30'

I wanna add on the selection min(pr."AsigStart")

Thanks

Upvotes: 1

Views: 873

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Your query has nothing to do with the question. But based on the question, I would recommend a lateral join:

select t1.*, t2.*
from table1 t1 left join lateral
     (select min(t2.start) as start, min(t2.end) as end
      from table2 t2
      where t2.id = t1.id
     ) t2
     on true;

Here is a db<>fiddle illustrating that the code works.

Upvotes: 2

Related Questions