CODEWITHSUNDEEP
sqlsql-servert-sql
Eshmam
Eshmam

Reputation: 53

Subquery returned more than 1

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

Getting that error for this piece of code: 

SELECT DISTINCT
    A.Author_Name, 
    C.Title, 
    P.Publisher_ID, 
    P.Publisher_Name, 
    P.Location
FROM Auth_Book_MN MN
INNER JOIN 
    Author A ON A.Author_ID=MN.Author_ID 
INNER JOIN 
    Library_Books L ON L.Book_ID=MN.Book_ID
INNER JOIN 
    Catalogue C ON L.ISBN=C.ISBN
INNER JOIN 
    Publisher P ON C.Publisher_ID=P.Publisher_ID
WHERE 
    MN.Author_ID = (SELECT Author_ID FROM Author WHERE Author_Name = 'McDonald' OR Author_Name='Ronald');

So basically I need it to list the author name(2 authors), book title, and some publisher details. I'm able to do it with this same piece of code if it's just one author at a time but not when it's >1. What should I do? I tried doing SELECT TOP 1 but that's just limiting my results to just the first author then.

Upvotes: 1

Views: 42

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I would use EXISTS with IN clause instead :

SELECT DISTINCT A.Author_Name, C.Title, P.Publisher_ID, P.Publisher_Name, P.Location
FROM Auth_Book_MN MN INNER JOIN 
     Author A 
     ON A.Author_ID = MN.Author_ID INNER JOIN 
     Library_Books L 
     ON L.Book_ID = MN.Book_ID INNER JOIN 
     Catalogue C 
     ON L.ISBN = C.ISBN INNER JOIN 
     Publisher P 
     ON C.Publisher_ID = P.Publisher_ID
WHERE EXISTS (SELECT 1 
              FROM Author A1 
              WHERE A.Author_ID = A1.Author_ID AND A.Author_Name IN ('McDonald','Ronald')
             );

Note : I have used IN clause instead of strange OR clause in subquery.

Upvotes: 0

user1178830
user1178830

Reputation: 446

Try using IN instead of =

SELECT DISTINCT
    A.Author_Name, 
    C.Title, 
    P.Publisher_ID, 
    P.Publisher_Name, 
    P.Location
FROM Auth_Book_MN MN
INNER JOIN 
    Author A ON A.Author_ID=MN.Author_ID 
INNER JOIN 
    Library_Books L ON L.Book_ID=MN.Book_ID
INNER JOIN 
    Catalogue C ON L.ISBN=C.ISBN
INNER JOIN 
    Publisher P ON C.Publisher_ID=P.Publisher_ID
WHERE 
    A.Author_ID IN (SELECT Author_ID FROM Author WHERE Author_Name = 'McDonald' OR Author_Name='Ronald');

Upvotes: 1

Related Questions