Mini Database- Subquery returned more than 1 value

Question

SELECT DISTINCT L.LecturerName
FROM Lecturer L
WHERE 5 > = 
(   SELECT  E.StudentID
    FROM    Class C, Enrolled E
    WHERE   C.ClassName = E.ClassName
    AND     C.LecturerID = L.LecturerID)

Error Message as below:-

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Anything wrong?

Answer 1

You need a COUNT(*) in the subquery. Also, Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.

SELECT DISTINCT L.LecturerName
FROM Lecturer L
WHERE 5 >= (SELECT COUNT(*)
            FROM Class C JOIN
                 Enrolled E
                 ON C.ClassName = E.ClassName
            WHERE C.LecturerID = L.LecturerID
           );

I am guessing that the DISTINCT is not needed in the SELECT.

You don't specify the database that you are using. More typically, you would put this information in the FROM or SELECT clauses so you could see the number of students:

SELECT L.LecturerName, COUNT(*) as NumStudents
FROM Lecturer L JOIN
     Class C
     ON C.LecturerID = L.LecturerID JOIN
     Enrolled E
     ON C.ClassName = E.ClassName
GROUP BY L.LecturerName
HAVING COUNT(*) <= 5;