Reputation: 119
I am trying to build a statement that will show the first_name
, last_name
, course_no
, description
for the instructor
that teaches
the most java courses. My max
statement shows the maximum number of courses however, when I executed the complete statement then I got no results.
Below is the Query:
SELECT first_name, last_name, course_no, description
FROM(
SELECT i.first_name, i.last_name, c.course_no, c.description
FROM instructor i INNER JOIN section s
ON i.instructor_id = s.instructor_id
JOIN course c ON c.course_no = s.course_no
WHERE c.description LIKE '%Java%')
GROUP BY first_name, last_name, course_no, description
HAVING COUNT(*) =
(SELECT MAX(course_count)
FROM (SELECT DISTINCT first_name, last_name, COUNT(*) As course_count
FROM(
SELECT i.first_name, i.last_name, c.course_no, c.description
FROM instructor i INNER JOIN section s
ON i.instructor_id = s.instructor_id
JOIN course c ON c.course_no = s.course_no
WHERE c.description LIKE '%Java%')
GROUP BY first_name, last_name));
Upvotes: 0
Views: 404
Reputation: 183241
You have a mismatch between your outer query and the subquery in your HAVING
clause: the former has GROUP BY first_name, last_name, course_no, description
, the latter only GROUP BY first_name, last_name
. Naturally, you won't generally find any 4-tuples of the former columns with as many rows as the most common pair of the latter columns.
Upvotes: 1