I’ve grown to love MySQL, but every now and then it has some strange restriction that temporarily stumps me. Then I discover a marvelous workaround and fall in love with it all over again.
This hack lets you overcome the restriction that you’re not allowed to have a subquery in FROM clause of a VIEW. The trick is to refactor the subquery into a view of its own, which you can then join to in the VIEW you want! The subquery is likely to be something you’ll re-use anyway, so this is doubly useful.
Here’s a worked example.
I’m working with some currency information for products that is (unfortunately) denormalised into columns instead of being Boyce-Codd normal form. You see this a lot in databases as it can make front-end code easier, but it makes it hard to work with in SQL.
It looks like this:
I’ve renamed and simplified tables and columns here to make the example clearer.
When payments come in, they’re in a specific currency and they may be discounted from the standard package price. I want to create a view that gives me exchange rates so I can report total revenues in a common currency. I need to use the exchange rate that reflects the prices stored in the packages.
I want to be able to join a payment that came in against a package with the effective exchange rate for that currency in that package, so I need a view that shows
package_id, currency, per_GBP.
To get this, I want to join a list of active currencies with the packages, and do a CASE on each currency to give the ratio in question. Easy enough, it looks like this:
-- NB: this DOESN'T WORK create or replace view exchange_rates as select packages.id , active_currencies.currency , CASE currency WHEN 'GBP' THEN 1.0 WHEN 'USD' THEN price_usd / price_gbp WHEN 'EUR' THEN price_eur / price_gbp WHEN 'CAD' THEN price_cad / price_gbp -- etc END as per_GBP from ( select distinct currency from payments) as active_currencies -- unfortunately currently ILLEGAL in MySQL join packages -- deliberate cross join ;
MySQL spits a dummy over this, but we can trick it into playing nicely like this:
create or replace view active_currencies as select distinct currency from payments ; create or replace view exchange_rates as select packages.id , active_currencies.currency , CASE currency WHEN 'GBP' THEN 1.0 WHEN 'USD' THEN price_usd / price_gbp WHEN 'EUR' THEN price_eur / price_gbp WHEN 'CAD' THEN price_cad / price_gbp -- etc END as per_GBP from active_currencies -- ah, that's better! join packages -- deliberate cross join ;
Now, when to convert payments to a common currency (GBP), I just join to my exchange_rates view on the package id and the currency and then use the exchange rate to convert to GBP!
Since I’m likely to use active currencies elsewhere, it’s actually a bonus to have this view. I suspect this technique may be an alternative use using this hack with stored procedures to solve restrictions with VIEWs containing group/count aggregate functions.