CODEWITHSUNDEEP
mysql
Will Barangi
Will Barangi

Reputation: 145

Subquery returns more than one row error result from mysql

I have a question, I am trying to go through a data base and display all the books by an author based on a search where I use an author name to get isbn and then find all the details of that isbn...well if the author wrote one book it is displaying one row but when the author wrote more than one book it is giving me an error...what am I doing wrong can you please help...Here is my code.

SELECT* FROM books WHERE isbn=(SELECT isbn FROM books_authors 
  WHERE author_id IN 
 (SELECT author_id FROM authors WHERE first_name ="J.K."))

Upvotes: 2

Views: 911

Answers (4)

ScaisEdge
ScaisEdge

Reputation: 133370

You can do this query avoinding subselect and in clause (using join) 

SELECT * FROM books 
INNER JOIN books_authors on books_authors.isbn = books.isbn
INNER JOIN authors on authors.author_id =  books_authors.author_id
WHERE authors.first_name = J.K.";

Anyway your error happen because you are using = instead of in for the first part of your quert SELECT* FROM books WHERE isbn=(SELECT isbn FROM ... i fyou want use your query you should use SELECT* FROM books WHERE isbn in (SELECT isbn FROM .....

Upvotes: 2

symcbean
symcbean

Reputation: 48357

NEVER use nested sub-queries when you can get the desired result using a join, particularly on MySQL:

SELECT b.* 
FROM books b
INNER JOIN books_authors ba
   ON b.isbn=ba.isbn
INNER JOIN authors a
   ON ba.author_id=a.author_id
WHERE a.first_name ="J.K.";

While you can use 'IN' inplace of '=' in your original query, the optimizer won't be able to do much with the query, it is inflexible and difficult to maintain.

Upvotes: 2

FDavidov
FDavidov

Reputation: 3675

Try: 

SELECT * FROM books 
 WHERE isbn IN (SELECT isbn FROM books_authors 
                  WHERE author_id IN (SELECT author_id 
                                        FROM authors 
                                       WHERE first_name ="J.K."
                                     )
               ) ;

Note that the outmost select should also have WHERE isbn IN....

Upvotes: 2

CLAbeel
CLAbeel

Reputation: 1088

Change to:

SELECT* FROM books WHERE isbn in (SELECT isbn FROM books_authors 
  WHERE author_id IN 
 (SELECT author_id FROM authors WHERE first_name ="J.K."))

You can't have isbn=(subquery) if the subquery returns multiple results.

Upvotes: 2

Related Questions