The SELECT view contains a subquery in the FROM clause.

Question

I have two tables and I need to create a view. Tables:

credit_orders(id, client_id, number_of_credits, payment_status) credit_usage(id, client_id, credits_used, date) 

For this, I use the following query. A query without the "create view" part works well, but with a "create view" it shows the error "View SELECT contains a subquery in the FROM clause". What could be the problem and possible solution:

 create view view_credit_status as (select credit_orders.client_id, sum(credit_orders.number_of_credits) as purchased, ifnull(t1.credits_used,0) as used from credit_orders left outer join (select * from (select credit_usage.client_id, sum(credits_used) as credits_used from credit_usage group by credit_usage.client_id) as t0 ) as t1 on t1.client_id = credit_orders.client_id where credit_orders.payment_status='Paid' group by credit_orders.client_id) 

Answer 1

According to the documentation:

MySQL Docs

• The SELECT statement cannot contain a subquery in the FROM clause.

A workaround is to create a view for each of your subqueries.

Then access these views from your view_credit_status

Answer 2

 create view view_clients_credit_usage as select client_id, sum(credits_used) as credits_used from credit_usage group by client_id create view view_credit_status as select credit_orders.client_id, sum(credit_orders.number_of_credits) as purchased, ifnull(t1.credits_used,0) as used from credit_orders left outer join view_clients_credit_usage as t1 on t1.client_id = credit_orders.client_id where credit_orders.payment_status='Paid' group by credit_orders.client_id) 

Answer 3

Since later MySQL documentation on view restrictions says:

Prior to MySQL 5.7.7, subqueries could not be used in the FROM clause of a view.

This means that choosing MySQL v5.7.7 or later or upgrading an existing MySQL instance to that version will completely eliminate this view restriction.

However, if you have a current version of MySQL for production that is earlier than v5.7.7, then removing this restriction on views should be only one of the criteria evaluated when deciding whether to upgrade. Using workarounds described in other answers may be a more viable solution - at least in a shorter time.

Answer 4

It seems to me that MySQL 3.6 gives the following error, while MySQL 3.7 no longer contains errors. I have not found anything in the documentation regarding this fix yet.