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)
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
toID
andTeacherName
toName
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:
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 SQLFiddleFor this to work you need to use a window function in the CTE and get rid of the
group by
: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.