MySQL Error Code: 1349. View's SELECT contains a subquery in the FROM clause

Question

I am trying to create a view but get the following error:

View's SELECT contains a subquery in the FROM clause

I am running the following command. I can't seem to figure out how to substitute the nested selects with joins. Any help would be much appreciated!

create view student_fee_basic as
select fsbc.*, ffp.name, ffp.amount 'fee'
from
(select sbc.*, ffc.name 'fname', ffc.id 'fid'
    from (select s.admission_no, s.first_name, bc.id 'bid', bc.code, bc.name
        from (select b.id, b.name, c.code
            from batches b, courses c
            where b.name = '2014-2015'
            and b.course_id = c.id) bc
        left join students s on bc.id = s.batch_id) sbc
    left join finance_fee_categories ffc on ffc.batch_id = sbc.bid
    where ffc.name = 'Basic Monthly') fsbc
left join finance_fee_particulars ffp on ffp.finance_fee_category_id = fsbc.fid;

Possible duplicate of [MySQL: View with Subquery in the FROM Clause Limitation](http://stackoverflow.com/questions/206062/mysql-view-with-subquery-in-the-from-clause-limitation) — Christophe Weis, Feb 02 '17 at 07:46

score 8 · Accepted Answer · answered May 20 '14 at 16:21

8

MySQL does not support subqueries in views:

Subqueries cannot be used in the FROM clause of a view.

The documentation is here.

The easiest fix is to use a series of different views for each level.

You can probably rewrite this query to remove the subqueries. However, I find it very hard to help without explicit joins.

answered May 20 '14 at 16:21

Gordon Linoff

1,242,037
58
646
786

Great! Thanks! I was able to construct the joins without using nested selects; seems I only needed the confidence that it can be done. – haanimasood May 20 '14 at 17:09

score 5 · Answer 2 · answered Sep 07 '17 at 14:56

5

Version 5.7 supports it. So one way to fix it is to migrate your database to newer version

answered Sep 07 '17 at 14:56

walv

2,680
3
31
36

score 0 · Answer 3 · answered Aug 29 '18 at 15:03

0

upgrade to mysql-8 and your problem is solved.

answered Aug 29 '18 at 15:03

LottaLava

889
1
9
21

MySQL Error Code: 1349. View's SELECT contains a subquery in the FROM clause

3 Answers3