How to join a table with a range of values from another table?

Question

I'm currently desperately looking to join two tables together. But usually, when you want to join two tables, they join only if the values themselves are equal on both sides. I'm looking for a join that joins if a value is within an interval. Let me explain:

I have these tables:

inscription(studentNumber, note)
thresVal(threshold, value)

table inscription looks a bit like this:

studentNumber | note 
--------------+------
123           | 78   
456           | 65   
789           | 52   
012           | 90   
345           | 37   

and tresVal looks like that:

 treshold | value
----------+--------
       90 |    4.3
       85 |      4
       80 |    3.7
       77 |    3.3
       73 |      3
       70 |    2.7
       65 |    2.3
       60 |      2
       57 |    1.7
       54 |    1.3
       50 |      1 
       35 |    0.5
        0 |      0

now, what I want is a join that checks in what range is the student's note and adds the row "value" accordingly, like that:

studentNumber | note | value
--------------+-------
123           | 78   | 3.3
456           | 65   | 2.3
789           | 52   | 1
012           | 90   | 4.3
345           | 37   | 0.5

As you can see the range is in between the value of a row and it's predecessor. a note of 65 is greater or equal to threshold 65, therefore it's value must be 2.3. All notes from 65 to 69 must be given the value of 2.3 and so on.

I just can't seem to come up with something good with this problem, all I can think of is hardcoding every possibility one after the other but there must be a simpler answer.

Answer 1

"Usually ... join only if the values themselves are equal on both sides" - so what! This results from earlier efforts to cause that condition, not be cause the natural is that way. And there is nothing requiring the usual way. It that doesn't suit you situation then set up join as you need. In this case you need to join on a range of values -- so do it.
The following will give you just that. The CTE uses window LEAD function to the be combine value from the next row the current row and creates a open, closed range. This ranged is then joined to inscription on the resulting range.

with tresVal_range as 
     (select int4range(treshold, coalesce(lead(tresHold) over(order by treshold), 10000),'[)') treshold, value
        from tresVal
     )
select i.studentnumber,i.note, t.value
  from inscription   i
  join tresval_range t
    on i.note <@ t.treshold
 order by i.studentnumber;