Why is my SELECT statement unable to find a table alias in one of its subqueries?-CodePudding

My current query is the following:

select course_id, sec_id
from (select course_id, sec_id, count(id) as enrollment
      from takes natural join section
      where semester ='Fall' and year = 2009
      group by course_id, sec_id
      ) as s_enrollment
where enrollment = (select max(enrollment) from s_enrollment);

Why does it say that no s_enrollment table is found even though I already have it in the from statement? I am assuming this error is caused by the reference to s_enrollment in the where clause.

Here are the schemas for the takes and section tables.

takes   CREATE TABLE takes
    (ID         varchar(5), 
     course_id      varchar(8),
     sec_id         varchar(8), 
     semester       varchar(6),
     year           numeric(4,0),
     grade              varchar(2),
     primary key (ID, course_id, sec_id, semester, year),
     foreign key (course_id,sec_id, semester, year) references section
        on delete cascade,
     foreign key (ID) references student
        on delete cascade
    )

section CREATE TABLE section
    (course_id      varchar(8), 
         sec_id         varchar(8),
     semester       varchar(6)
        check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
     year           numeric(4,0) check (year > 1701 and year < 2100), 
     building       varchar(15),
     room_number        varchar(7),
     time_slot_id       varchar(4),
     primary key (course_id, sec_id, semester, year),
     foreign key (course_id) references course
        on delete cascade,
     foreign key (building, room_number) references classroom
        on delete set null
    )

CodePudding user response：

s_enrollment is a derived table alias, you can't use in on same select statement, but you can use it if its joined or in a subquery.

another way of getting what you want is to sort by enrollment, in here you can get the max value.

select course_id, sec_id, enrollment
from (select course_id, sec_id, count(id) as enrollment
      from takes natural join section
      where semester ='Fall' and year = 2009
      group by course_id, sec_id
      ) as s_enrollment
order by enrollment desc limit 1;

CodePudding user response：

Your select statement contains two subqueries. Each of these subqueries can't "see" outside of the parentheses it's contained in. So when the second subquery references s_enrollment, it says that s_enrollment can't be found because no table called s_enrollement is created within that subquery's set of parentheses—s_enrollment is only created as a result of the first subquery.

If I understand correctly the intent of your query, this should produce the result you want:

select course_id, sec_id
from takes natural join section
where semester ='Fall' and year = 2009
group by course_id, sec_id
order by count(id) desc limit 1