A neat MySQL hack to create a VIEW with subquery in the FROM clause…

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.