Tom Senner Tom Senner - 1 month ago 10
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.

hotel

hotelID city name
2 London Hotel-London-Inn


bed

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


currency

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 hotel.city = 'London' ORDER BY bed.price ASC

Answer

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

     SELECT 
          hotel.*
        , 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 hotel.city = 'London' 
        ORDER BY bed.price * currency.oneDollar  ASC