Is there a better way of this MySQL CASE SELECT ?

Question

The last two parameters are PHP WHERE and ORDER clauses, they (probably) don't matter here.

fornecedores.nome_emp AS nome_fornecedor, exchange_rates1.rate AS rateEUR_USD,
exchange_rates2.rate AS rateEUR_AOA,
CASE produtos.moeda 
WHEN 'AOA' THEN produtos.preco_custo / exchange_rates2.rate 
WHEN 'EUR' THEN produtos.preco_custo 
WHEN 'USD' THEN produtos.preco_custo / exchange_rates1.rate 
END as prc, 

CASE produtos.moeda 
WHEN 'AOA' THEN produtos.preco_venda / exchange_rates2.rate 
WHEN 'EUR' THEN produtos.preco_venda 
WHEN 'USD' THEN produtos.preco_venda / exchange_rates1.rate 
END as pvp 

FROM produtos 
LEFT JOIN fornecedores ON produtos.id_fornecedor = fornecedores.id_fornecedores 
LEFT JOIN exchange_rates AS exchange_rates1 ON exchange_rates1.para = 'USD' 
LEFT JOIN exchange_rates AS exchange_rates2 ON exchange_rates2.para = 'AOA'
$whereClause $orderClause

Thanks in advance :D

Answer 1

You're missing the power of a JOIN in MyQL. You're trying to get two different exchange rates (via two different LEFT JOINs to the same table) and then use the CASE to figure out which one to use.

Instead, join only to the rate you actually need! Then you don't need any conditional logic at all.

fornecedores.nome_emp AS nome_fornecedor, exchange_rates.rate, produtos.preco_custo / exchange_rates.rate
FROM produtos
LEFT JOIN exchange_rates ON exchange_rates.para = produtos.moeda

This assumes that there's a row in exchange_rates for EUR that has the rate set to 1.