look up dynamic value from range in another table-CodePudding

I have 2 tables. The first one is a detail table with years and actual limit values. The second table has max control limits but only for certain years.

Table 1 & 2

What I want to do is list all of the detail records but pull in the values of the control limits if the year is less than the next one listed in the control table.

Desired results: results

I have tried this query but it duplicates 2015, 2016 and 2017.

SELECT d.id, d.yeard, d.value, c.Column1
FROM detailTbl d
RIGHT OUTER JOIN controlTbl c ON d.dated <= c.datec

CodePudding user response：

You may use Row_Number() function as the following to remove duplicates:

with cte as
(
  Select D.id,D.yeard,D.val, C.limitVal, 
  row_number() over (partition by D.id order by C.yeard desc) as rn from
  detailTbl D left join controlTbl C
  on D.yeard>=C.yeard
)

Select B.id,B.yeard,B.val,B.limitVal from
cte B where B.rn=1 order by B.id

See a demo on MySQL 8.0 from here.