Tom Senner Tom Senner - 1 year ago 72
MySQL Question

MySQL: Join 3 Tables and multiply price with currency before ordering as new field

I have 3 tables (simplified below) that I want to join and multiply bed.price with currency.oneDollar and then order as a new field priceInDollar.


hotelID city name
2 London Hotel-London-Inn


bedID hotelID room price cur
1 2 single room 10 USD
1 2 double room 100 MXN


id cur oneDollar
1 USD 1
2 MXN 0.052605

This joins hotel and bed like I want but canĀ“t figure out how to multiply the price to dollar as a new field and order that.

SELECT hotel.*, bed.*
FROM hotel JOIN bed
ON hotel.hotelID = bed.hotelID
WHERE = 'London' ORDER BY bed.price ASC

Answer Source

You need another join in the sample you get all the currency value but you can add more where condition for filter

        , bed.*
        , bed.price
        , currency.cur
        , bed.price * currency.oneDollar as priceInDollar
        FROM hotel 
        INNER JOIN bed ON hotel.hotelID = bed.hotelID
        INNER JOIN currency on currency.cur = bed.cur 
        WHERE = 'London' 
        ORDER BY bed.price * currency.oneDollar  ASC
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download