Dividing and multiplying columns in mySQL

Question

This has been bugging me for some time i have following two tables:

Table: conv
---------------
id       cur     toEUR
--------------------------------------------
1       EUR       1
2       GBP       0.85
3       USD       1.32
--------------------------------------------

Table: itms
--------------
id       cur       price
--------------------------------------------
1       USD       5
2       USD      10
3       EUR       2.5
---------------------------------------------

Basically I need to divide the appropriate values...

SELECT itms.price/conv.toEUR 
  FROM itms
     , conv 
 where itms.cur=conv.cur

...from 'itms' with the ones from 'conv' and multiply by the value I want to convert to in 'conv'.
Is this possible purely in SQL? I have attempted a few times, but I'm not sure what the best approach is..
Any hints are appreciated!

Best Regards

Answer 1

Is this what you're looking for?

select 
  i.id,
  c2.cur,
  i.price * c2.toEUR / c.toEur
from itms i
  join conv c on i.cur = c.cur
  join conv c2 on c2.cur = 'GBP'
order by i.id

• SQL Fiddle Demo