i am having trouble with complex sort what i need to is check if the currency is not AUD, if not multiply it by the correct exchange rate to convert it to australian dollars and then do a sort on it.
I am not sure what i am doing wrong, please help me.
Select cruises.id as cruise_id,
special_header as name,
cruises.length as nights,
currencies.name as currency,
ships.name as ship,
cruiselines.name as cruiseline,
cruiselines.id as cl_id,
destinations.name as destination,
min(sailingdates.sailingdate) as sailingdate,
exchangerates.currency_from as ex_rate,
if('currency!=AUD', 'ex_rate*min(specialspricing.price)', 'min(specialspricing.price)') as price
from cruises
left join currencies on cruises.currency_id = currencies.id
left join ships on ships.id = cruises.ship_id
left join cruiselines on cruiselines.id = cruises.cruiseline_id
left join destinations on destinations.id = cruises.destination_id
left join sailingdates on cruises.id = sailingdates.cruise_id
left join specialspricing on cruises.id = specialspricing.cruise_id
left join exchangerates on exchangerates.currency_from = currencies.name
where on_special = 'y'
AND sailingdates.special = 'y'
AND sailingdates.sailingdate > now()
and cruises.id = specialspricing.cruise_id
