SELECT min max and maximum value min from part of a table in MySQL

Question

I want to select the min max and max min values ​​for each value in table 1 from another table2. sample entry

table2 name, value,y f1, .01,.04 f1,.02,.05 f1,.05,.06 f1,.45,.07 f2,.03,.09 f2,.05,.02 table1 name, value f1, .04 f2,.04 expected output table3 name, value,min_max-value,max_min-value,y(min_max-value),y(max_min-value) f1, .04,.02,.05,.05,.06 f2,.04,.03,.05,.09,.02 

Answer 1

You can get the MAX and MIN values ​​from t2 with the following query:

 SELECT t1.name, t1.value, (SELECT min(value) FROM table2 t2 WHERE t2.name = t1.name), (SELECT MAX(value) FROM table2 t2 WHERE t2.name = t1.name) FROM table1 t1 

EDIT: to get the values ​​closer to the t1.value value and the corresponding y value, you can do:

 SELECT t1.NAME, t1.value, (SELECT MAX(t2.value) FROM table2 t2 WHERE t2.NAME = t1.NAME AND t2.value < t1.value) AS maxmin, (SELECT MIN(t2.value) FROM table2 t2 WHERE t2.NAME = t1.NAME AND t2.value > t1.value) AS minmax, (SELECT t2.y FROM table2 t2 WHERE t2.NAME = t1.NAME AND t2.value = ( SELECT MAX(t3.value) FROM table2 t3 WHERE t3.NAME = t1.NAME AND t3.value < t1.value )) AS ymaxmin, (SELECT t2.y FROM table2 t2 WHERE t2.NAME = t1.NAME AND t2.value = ( SELECT MIN(t3.value) FROM table2 t3 WHERE t3.NAME = t1.NAME AND t3.value > t1.value )) AS yminmax FROM table1 t1 

see this fiddle