SQL inline view subquery

Is it possible to reference an inline view defined in the "FROM" clause from a subquery in the WHERE clause?

SELECT tmp.TeacherName,
       tmp.courseid,
       tmp.AvgAttendingStudents
FROM   (SELECT T.TeacherID              AS ID,
               T.TeacherName            AS Name,
               C.CourseID               AS CourseID,
               avg(L.AttendingStudents) AS AvgAttendingStudents
        FROM   Teachers AS T
               join Courses AS C
                 ON C.TeacherID = T.TeacherID
               join Lessons AS L
                 ON L.CourseID = C.CourseID
        GROUP  BY T.TeacherID,
                  C.CourseID) AS tmp
WHERE  tmp.AvgAttendingStudents = (SELECT max(AvgAttendingStudents)
                                   FROM   tmp AS tmp2
                                   WHERE  tmp2.TeacherID = tmp.TeacherID);

In this example i'm trying to list all the teachers and for each of them I want to show the course having the maximum average of attending students (calculated on all lessons). I tried to use an inline view (tmp) to calculate the average number of attending students for each course, but I don't know if I can reference that view in the subquery SELECT max(...). I need this to work with Oracle, but unfortunately at the moment I don't have any Oracle database to try it. I tried with MySQL (since I don't think I'm using any Oracle-specific features), but as expected I get the error "Table 'db.tmp' doesn't exist". Is this somehow possible with Oracle?

Here's my example schema:

CREATE TABLE Courses
  (
     CourseID   INTEGER PRIMARY KEY,
     CourseName VARCHAR(32),
     TeacherID  INTEGER
  );

CREATE TABLE Teachers
  (
     TeacherID   INTEGER PRIMARY KEY,
     TeacherName VARCHAR(32)
  );

CREATE TABLE Lessons
  (
     LessonDate        TIMESTAMP,
     CourseID          INTEGER,
     AttendingStudents INTEGER,
     PRIMARY KEY (LessonDate, CourseID)
  );

(Sorry for my bad english)

Solution

You are right in that you can't reference the derived table ("inline view") that way. You need to rewrite the derived table ("inline view") to a common table expression:

You also have other errors in there. In the derived table you rename TeacherID to ID and TeacherName to Name so you need to use those column names not the "real" ones.

Also Oracle doesn't support AS for a table alias, so you need to get rid of those as well.

So a direct rewrite of the statement would be:

with tmp as (
  SELECT T.TeacherID              AS ID,
         T.TeacherName            AS Name,
         C.CourseID               AS CourseID,
         avg(L.AttendingStudents) AS AvgAttendingStudents
  FROM   Teachers T
         join Courses C
           ON C.TeacherID = T.TeacherID
         join Lessons L
           ON L.CourseID = C.CourseID
  GROUP  BY T.TeacherID,
            C.CourseID
)
SELECT tmp.name,
       tmp.courseid,
       tmp.AvgAttendingStudents
FROM tmp
where tmp.AvgAttendingStudents = (SELECT max(AvgAttendingStudents)
                                  FROM   tmp tmp2
                                  WHERE  tmp2.id = tmp.id);

However the above will not work in Oracle because of the invalid use of the group by and aggregate function. The above will result in "ORA-00979: not a GROUP BY expression", see this SQLFiddle

For this to work you need to use a window function in the CTE and get rid of the group by:

with tmp as (
  SELECT T.TeacherID              AS ID,
         T.TeacherName            AS Name,
         C.CourseID               AS CourseID,
         avg(L.AttendingStudents) over (partition by t.teacherid, c.courseid) AS avgattendingstudents 
  FROM   Teachers T
         join Courses C
           ON C.TeacherID = T.TeacherID
         join Lessons L
           ON L.CourseID = C.CourseID
)
SELECT tmp.name,
       tmp.courseid,
       tmp.AvgAttendingStudents
FROM tmp
where tmp.AvgAttendingStudents = (SELECT max(AvgAttendingStudents)
                                  FROM   tmp tmp2
                                  WHERE  tmp2.id = tmp.id);

See this SQLFiddle for an example.

Note that you can not test the above queries with MySQL because it does not support modern SQL like common table expressions or window functions.

But you can use the SQLFiddle examples to test it with data.