How to organize search and sort selection from the database by currency?

Question

SQL

People, welcome.
There is a "Goods" table with the following structure:
[id, price, price_currency]
and approximately the following content:
[1, 100, RUR]
[2, 2, USD]
There can be an unlimited number of currencies, but their quotes are not stored in the database.
Actually, the question is how to organize data storage, or work with such a table in such a way that it would be convenient to sort and search using a single currency (for example, USD) as the base one? In other words, it is necessary to somehow bring all prices to a single value in the USD currency before the selection using an external quote.
There is an idea to add the price_base field to the table and store the price in the base currency in it, as well as recalculate this value once a day, but perhaps there is a simpler solution?

Answer 1

SELECT (catalog.price+currency.value) as summ 
FROM catalog 
LEFT JOIN currency 
ON catalog.price_currency = currency.ident

*Wrote from memory. Take a look at the documentation