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:

denormalised prices

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.

Advertisements

6 thoughts on “A neat MySQL hack to create a VIEW with subquery in the FROM clause…

  1. That’s exactly what I did in my query. I was trying to left outer join two sub-queries with an ON condition inside a view – so I created a view for each subquery, then used both the views to do the LOJoin. Left Query has 1 record, Right query has 100 records, instead of returning 1 record, it is returning 100 records with the left record repeated 100 times.

    I ran into your blog when I was trying to find a solution. If you already ran into anything like that, please suggest a work-around. Appreciate your help! Cheers..

    Like

  2. Hey,
    I had the same idee 😉 I put my 2 subqueries in own views and joined them. But now the duration time increase by 400%!! I don’t know why 😦 Do you have an idea? Thanks!

    Like

  3. Unfortunately you’ll end up polluting your schema with those subviews. Instead, I’ll rather have a stored procedure that will create/update a materialized view (table in MySQL) and which will be executed by triggers.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s