CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2014
Yap Chun Ee
Yap Chun Ee

Reputation: 13

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?

Upvotes: 1

Views: 62

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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;

Upvotes: 3

Joe Taras
Joe Taras

Reputation: 15379

You can join in the main query Lecturer and Class and apply a distinct, so you restrict your query about Enrollment with less 5 students (with a subquery)

Try this:

SELECT DISTINCT L.LecturerName
FROM Lecturer L
JOIN Class C
    ON L.LecturerID = C.LecturerID
WHERE
    (SELECT count(E.ClassName)
     FROM Enrollment E
     WHERE C.ClassName = E.ClassName
     AND C.LecturerID = L.LecturerID) < 5

Upvotes: 3

Related Questions