View s SELECT contains a subquery in the FROM clause

Question

I have two tables and I need to create a view  The tables are   credit orders id  client id  number of credits  payment status  credit usage id  client id  credits used  date    I use the following query to do this  The query without the  create view  part works well but with  create view   it shows the error  View s SELECT contains a subquery in the FROM clause   What could be the issue  amp  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

User · Answer

Looks to me as MySQL 3 6 gives the following error while MySQL 3 7 no longer errors out  I am yet to find anything in the documentation regarding this fix

User · Answer

As the more recent MySQL documentation on view restrictions says      Before MySQL 5 7 7  subqueries cannot be used in the FROM clause of a view     This means  that choosing a MySQL v5 7 7 or newer or upgrading the existing MySQL instance to such a version  would remove this restriction on views completely   However  if you have a current production MySQL version that is earlier than v5 7 7  then the removal of this restriction on views should only be one of the criteria being assessed while making a decision as to upgrade or not  Using the workaround techniques described in the other answers may be a more viable solution - at least on the shorter run

User · Answer

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

User · Answer

As per documentation    MySQL Docs   The SELECT statement cannot contain a subquery in the FROM clause    Your workaround would be to create a view for each of your subqueries   Then access those views from within your view view credit status

[mysql] View's SELECT contains a subquery in the FROM clause

Examples related to mysql

Examples related to sql

Examples related to database

Examples related to views