MySQL: Matching inexact values using "ON"
- by Brad
I'm way out of my league here...
I have a mapping table (table1) to assign particular values (value) to a whole number (map_nu). My second table (table2), is a collection of averages (avg)
(I couldn't figure out how to properly make a markdown table, please feel free to edit!)
table1: table2:
(value)(Map_nu) (avg)
---- -----
1 1 1.111
1.045 2 1.2
1.09 3 1.33333
1.135 4 1
1.18 5 1.389
1.225 6 1.42
1.27 7 1.07
1.315 8
1.36 9
1.405 10
I need to find a way to match the averages from table2 to the closest value in table1. It only need to match to the 2 digit past the decimal, so I've added the Truncated function
SELECT map_nu
FROM `table1`
JOIN table2 ON TRUNCATE(table1.value,2)=TRUNCATE(table2.avg,2)
I still miss the values that don't match the averages exactly. Is there a way to pick the nearest truncated value?
Thanks!
