CODEWITHSUNDEEP
sqloracle-databasesubquery
tworley1977
tworley1977

Reputation: 311

Nested Subqueries in Oracle

I've got a nested subquery that I can't seem to figure out. Here is the problem:

"Using the BOOK_AUTHOR and AUTHOR tables for the upper most query, create a sub query that will return the BOOKID, and the first and last name of the author(s) who have written the book(s) that have been purchased the most. To successfully accomplish this, you will need to first find out the maximum total number of copies (based on quantity) sold of a book (based on bookid) in the ORDER_ITEMS table. This will be done in the bottom most query. The return from this query will be used to compare against the BOOKID in the ORDER_ITEMS table that occurs an equal number of times in the middle query. The return of this query will then be used to compare to a BOOKID related to an Author in the tables of the top most query. (HINT: Be careful, you will need both GROUP BY and HAVING clauses for your solution.)"

Here is the statement that I have so far:

SELECT ba.bookid, a.firstname, a.lastname
FROM book_author ba, author a
WHERE bookid =
(SELECT bookid, quantity
FROM order_items
WHERE quantity IN
    (SELECT bookid, SUM(quantity)
    FROM order_items
    GROUP BY bookid);

The bottom query is right as far as I can tell. It returns a list of book ids with a total number of copies sold for each id.

Now assuming that I'm reading the problem correctly, the middle query should pull the book id(s) from the list returned by the bottom query, that has the most copies sold. This is where I'm stuck. I know the middle query I have written above doesn't work. When I try to test the middle and bottom queries together I get a ORA-913 error (too many values).

Can anyone offer any insight into what I'm missing??

Thanks.

Upvotes: 0

Views: 379

Answers (2)

Raging Bull
Raging Bull

Reputation: 18747

Problem:

You cannot use = against a subquery (unless it returns only one value).

Solution:

In this case, use IN. And you should use MAX to find the maximum value.

SELECT ba.bookid, a.firstname, a.lastname
FROM book_author ba, author a
WHERE bookid IN
(SELECT bookid
FROM order_items
WHERE quantity IN
    (SELECT MAX(quantity) as quantity
    FROM order_items
    GROUP BY bookid);

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

I had to improvise some field names since you didn't add the schema, but the description is talking about a query similar to this. Note that MAX(SUM()) may not work on all RDBMS's, but works well with Oracle.

SELECT ba.bookid, a.firstname, a.lastname
FROM book_author ba
JOIN author a ON a.id = ba.authorid
WHERE bookid IN
  (SELECT bookid
   FROM order_items
   GROUP BY bookid
   HAVING SUM(quantity) = 
     (SELECT MAX(SUM(quantity))
      FROM order_items
      GROUP BY bookid));

The first (bottom-most) query selects how many books the best selling book has sold (ie the sum of all the quantities for all books, and just pick the max quantity)

The second (mid) query picks the bookid's of all books that have sold the highest quantity (could be a tie, so we may get several)

To last (topmost) query just picks the book id and author details for those books.

An SQLfiddle to test with.

Upvotes: 0

Related Questions