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


Gruff's Tech Tips

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…

View original post 347 more words