FROM clause in view error

Question

I am getting an error while creating the following view

    create or replace view viewfee as
    select unique_id,session,course_cat,course,class,rollno, sum(adm_due) as adm_due, sum(tuition_due) as tuition_due, sum(exam_due) as exam_due from 
    (
    select unique_id,session,course_cat,course,class,rollno,null as adm_due, null as tuition_due, null as exam_due from tbstdinfo
    union all
    select unique_id,null,null,null,null,null,(adm_tot - sum(adm_payment)) AS adm_due, null, null from tbfeeadmission group by unique_id
    union all
    select unique_id,null,null,null,null,null,null, (tuition_tot - sum(tuition_payment)) AS tuition_due, null from tbfeetuition group by unique_id
    union all
    select unique_id,null,null,null,null,null,null, null, (exam_tot - sum(exam_payment)) AS exam_due from tbfeeexam group by unique_id
    )x
    group by unique_id

but if i execute the query without view, it works fine

select unique_id,session,course_cat,course,class,rollno, sum(adm_due) as adm_due, sum(tuition_due) as tuition_due, sum(exam_due) as exam_due from 
(
select unique_id,session,course_cat,course,class,rollno,null as adm_due, null as tuition_due, null as exam_due from tbstdinfo
union all
select unique_id,null,null,null,null,null,(adm_tot - sum(adm_payment)) AS adm_due, null, null from tbfeeadmission group by unique_id
union all
select unique_id,null,null,null,null,null,null, (tuition_tot - sum(tuition_payment)) AS tuition_due, null from tbfeetuition group by unique_id
union all
select unique_id,null,null,null,null,null,null, null, (exam_tot - sum(exam_payment)) AS exam_due from tbfeeexam group by unique_id
)x
group by unique_id

Actually i need to combine four tables to get all desired records in a single table. please help

#1349 - View's SELECT contains a subquery in the FROM clause — Subimal Sinha, Mar 17 '16 at 07:39
Create view cannot have subquery in from. Look at this for workaround: http://stackoverflow.com/questions/8428641/views-select-contains-a-subquery-in-the-from-clause — White Feather, Mar 17 '16 at 07:51

score 1 · Answer 1 · answered Mar 17 '16 at 07:45

1

Before MySQL 5.7.7, the SELECT statement cannot contain a subquery in the FROM clause.

Reference:

http://dev.mysql.com/doc/refman/5.7/en/create-view.html

answered Mar 17 '16 at 07:45

Dylan Su

5,975
1
16
25

score 1 · Answer 2 · answered Mar 17 '16 at 08:15

You cannot have subselects in view definition, however you could do something like this:

create or replace view intermediate_view as
select unique_id,session,course_cat,course,class,rollno,null as adm_due, null as tuition_due, null as exam_due from tbstdinfo
union all
select unique_id,null,null,null,null,null,(adm_tot - sum(adm_payment)) AS adm_due, null, null from tbfeeadmission group by unique_id
union all
select unique_id,null,null,null,null,null,null, (tuition_tot - sum(tuition_payment)) AS tuition_due, null from tbfeetuition group by unique_id
union all
select unique_id,null,null,null,null,null,null, null, (exam_tot - sum(exam_payment)) AS exam_due from tbfeeexam group by unique_id

and then

create or replace view viewfee as
select unique_id,session,course_cat,course,class,rollno, sum(adm_due) as adm_due, sum(tuition_due) as tuition_due, sum(exam_due) as exam_due from intermediate_view
group by unique_id

as a workaround

I did this already but i want in a single view, anyway thanks — Subimal Sinha, Mar 17 '16 at 09:41

FROM clause in view error

2 Answers2

Reference: