Subquery returns more than one row SQL

Question

Hi there,

I have executed a code

SELECT CASE b.ON_LOAN
    when 'Y' then
        'In Lib'
    when 'N' then 
        (SELECT c.duedate from book_copy a, book b, loan c
            where b.isbn = 123456
            and a.isbn = b.isbn 
            and a.book_no = c.book_no)
    END AS Availability, a.isbn, a.class_number 
FROM book_copy b, book a
where a.isbn = b.isbn and a.isbn = 123456

it returns an error saying subquery returns more than one row . I am trying to get the availability for a book. A book can have more than one copy, that is identified by its book_no. If a copy is available it should retrun just 'In lib' otherwise, the duedate from loan table. E.g if a book has three copies, 2 out and 1 in lib, i want my query to show all three copies. I think i am missing an outer join. Could you please clarify.

My tables that i use for this are

book_copy: book_no, isbn, on_loan
loan: student_id, book_no, duedate,datereturned,loan_id
    fk: book_no with book_no in book_copy
book: isbn (pk), title, class

thanks, rk

Answer 1

The problem is in:

(SELECT c.duedate from book_copy a, book b, loan c where b.isbn = 123456 and a.isbn = b.isbn and a.book_no = c.book_no)

You actually only want the loan table, but use MAX to make sure it only returns one row.

(SELECT MAX(c.duedate) from loan c where a.book_no = c.book_no)

So... you can hook into the tables in the outer query - no need to use a and b again.

Rob

Answer 2

I would first get rid of those implied joins. Then I would use a derived table instead of a correlated subquery (Never use a correlated subquery they are performance dogs!)

SELECT 
 CASE b.ON_LOAN    
  WHEN 'Y' THEN 'In Lib'    
  WHEN 'N' THEN c.duedate END
  AS Availability, 
 a1.isbn, 
 a.class_number 
FROM book_copy b
JOIN book A1   
 ON a1.isbn = b.isbn 
JOIN (SELECT MIN(c.duedate), c.isbn  FROM loan c 
   join  book a 
   on a.a.isbn = c.isbn    
     WHERE a.isbn = 123456 AND datereturned is null
  GROUP BY  c.isbn 
  ) c 
 ON a1.isbn =  c.isbn 
WHERE a.isbn = 123456

Originally, I used max because you have given us no indciator of how to chosse which of the records in the loan table to pick when you only want one. However, I suspect there is a better way (or at least I hope your design has a better way) to find the book that is out. Maybe you have a field that indicates the book has been returned or maybe you want the date of the first book that is available to be returned not the latest date to be returned. Don't use max without thinking about how to get just one record. Otherwise you may be writing a query that works but it is incorrect in its results. Based on your comment below I have revised the query.

The key to understanding this is that the derived table should bring back the record that has not yet been returned but which should be available soonest (ie it has the earliest due date). If my query doesn't do that, then you will need to experiement until you find one that does. The final where clause may or may not be necessary, you should check that as well. It depends on whether the book numbers are unique or if they are repeated for differnt books.