Yap Chun Ee Yap Chun Ee - 9 months ago 30
SQL Question

Mini Database- Subquery returned more than 1 value

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 Source

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;