Nested Views issue in mysql

Question

CREATE or REPLACE VIEW myView AS
SELECT t2.film_id, t2.no_actors AS n_actor, COALESCE(t1.no_copies,0) AS n_inventory FROM
(SELECT film_id, count(film_id) AS no_copies FROM inventory GROUP BY film_id) AS t1 RIGHT OUTER JOIN 
(SELECT film_id, count(actor_id) 
AS no_actors FROM film_actor GROUP BY film_id) AS t2 
ON t2.film_id=t1.film_id;

The above query gives the error:

ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

How do we write nested views for the above query in one single query ? Please help. Thanks in advance.

I have tried all possible ways...posting it here for help is the last thing I could do. Please help. — Pratik Gaurav, Nov 19 '16 at 19:08
Odd, when I highlight your error, right click and select "search on google" - I get numerous results that are solutions. Isn't that peculiar. — N.B., Nov 19 '16 at 19:09
All those responses tell me to create a view separately and use that view in the main query. But, I want it in a single query where all the views r nested and the output thereafter. — Pratik Gaurav, Nov 19 '16 at 19:11
All subqueries can be written as JOINs. Read the other answers. — N.B., Nov 19 '16 at 20:02

score 1 · Accepted Answer · answered Nov 19 '16 at 19:19

You can't use subquery in create view but you can use others views this way

CREATE or REPLACE VIEW myViewSub1 AS 
SELECT film_id, count(film_id) AS no_copies FROM inventory GROUP BY film_id
;


CREATE or REPLACE VIEW myViewSub2 AS 
SELECT film_id, count(actor_id) 
AS no_actors FROM film_actor GROUP BY film_id
;

CREATE or REPLACE VIEW myView AS
SELECT t2.film_id, t2.no_actors AS n_actor, COALESCE(t1.no_copies,0) AS n_inventory 
FROM  myViewSub1 AS t1 
RIGHT OUTER JOIN myViewSub2 AS t2 
ON t2.film_id=t1.film_id
;

Nested Views issue in mysql

1 Answers1