In MySQL 5.0 why does the following error occur when trying to create a view with a subquery in the FROM clause?
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
If this is a limitation of the MySQL engine, then why haven't they implemented this feature yet?
Also, what are some good workarounds for this limitation?
Are there any workarounds that work for any subquery in the FROM clause or are there some queries that can not be expressed without using a subquery in the FROM clause?
An example query (was buried in a comment):
SELECT temp.UserName
FROM (SELECT u1.name as UserName, COUNT(m1.UserFromId) as SentCount
FROM Message m1, User u1
WHERE u1.uid = m1.UserFromId
Group BY u1.name HAVING SentCount > 3 ) as temp
Best Answer
I had the same problem. I wanted to create a view to show information of the most recent year, from a table with records from 2009 to 2011. Here's the original query:
Outline of solution:
Here's the solution query:
It works fine on mysql 5.0.45, without much of a speed penalty (compared to executing the original sub-query select without any views).