Subquery returns more than one SQL row
I have executed the 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 indicating that the subquery is returning more than one row. I am trying to get accessibility for a book. A book can have more than one copy, which is identified by its book_no. If a copy is available, it should only fetch "In lib" otherwise, duedate from the loan table. For example, if a workbook has three copies, 2 from 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 I am using 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 gk
I would get rid of these implied unions first. 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
I originally used max because you didn't tell us to tell us which of the entries in the loan table to pick when you just want to. However, I suspect there is a better way (or at least I hope your design is better) to find the book that is missing. Perhaps you have a field that says the book has been returned, or perhaps you want the date of the first book that is available for return, rather than the last date to be returned. Don't use max without thinking about how to get just one record. Otherwise, you can write a query that works but is incorrect in its results. Based on your comment below, I revisited the request.
The key to understanding this is that the view should return a record that has not yet been returned, but that should be available soon (that is, it has the earliest date). If my query doesn't do that, you will need to try until you find what does. At the end, where the proposal may or may not be necessary, you should check it out. It depends on whether the book numbers are unique or repeated for different books.
source to share
The problem is this:
(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 to get 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 connect to tables in an outer query - no need to use a and b again.
Rob
source to share