sqlite - Recursive Math in MySQL -

i apologize if not using right term.

anyway, have 2 tables. 1 item table id , value. second mod table link item table , modifier. there may multiple modifiers given item. if item.value 100 , 2 modifiers linked item (say mod.modifier value of 1.25 , 1.1) value 137.5 (100 *1.25 *1.1) rather 135 (100 *(1.25+1.1)).

this test code have written far:

drop table if exists item; drop table if exists mod;  create table item (     item_id integer primary key,     value decimal );  create table mod (     mod_id integer primary key,     item_id integer,     modifier decimal );  insert item (item_id, value)     values (0, 100); insert item (item_id, value)     values (1, 10); insert item (item_id, value)     values (2, 1000);  insert mod (mod_id, item_id, modifier)     values (0, 0, 0.25); insert mod (mod_id, item_id, modifier)     values (1, 0, 0.10); insert mod (mod_id, item_id, modifier)     values (2, 2, 0.10); insert mod (mod_id, item_id, modifier)     values (3, 2, 0.05); insert mod (mod_id, item_id, modifier)     values (4, 2, 0.15);  select item.item_id id, item.value value, mod.modifier mod     item     left join mod     on item.item_id = mod.item_id; 

i hope result like:

item.item_id    item.value      result 0               100             137.5 1               10              10 2               1000            1328.25 

so far know mysql lacks built-in aggregate function one. can construct it:

select item.item_id id, item.value value, exp(sum(log10(mod.modifier)), 10) mod     item     left join mod     on item.item_id = mod.item_id group item.item_id; 

because log() maps addition multiplication.