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…
View original post 347 more words