Can a different SubQuery replace a Set function Minus?
682150Apr 26 2009 — edited Apr 27 2009I'm a student in an Oracle SQL class using 10g. We are nearing the end of our class.
We are working on a chapter on Subqueries.
In one of the homework problems I have a solution that uses a Subquery.
But it also uses a Minus. We studied the Set functions earlier. So my solution
does use things we have already studied. I'm just wondering if the whole
problem could be solved with a different use of Subqueries.
And eliminating the use of the Minus.
Here is the question - 'List the title of all books in the same category as books previously
purchased by customer 1007. Do not include books already purchased by this customer.'
And here is my solution:
SELECT InitCap(Title) AS "Book Title",
Category
FROM Books
WHERE Category IN
( SELECT DISTINCT(Category)
FROM Books JOIN OrderItems USING (ISBN)
JOIN Orders USING (Order#)
JOIN Customers USING (Customer#)
WHERE Customer# = 1007 )
Minus
SELECT InitCap(Title),
Category
FROM Books JOIN OrderItems USING (ISBN)
JOIN Orders USING (Order#)
JOIN Customers USING (Customer#)
WHERE Customer# = 1007
ORDER BY Category;
There is nothing tricky about the tables.
Customers has Customer# which Joins to Orders via the Customer#.
Orders Joins to a table called OrderItems via the Order#.
OrderItems is also joined to Books via the ISBN.
So to get the details of an Order (like which specific books and quantities)
we have to get to Orderitems which has the ISBN and the Quantity.
But every time I go back and look at this question I keep seeing the answer as
'Find the large group, take out the part we don't need, leaving the answer'.
Well, I hope I gave enough explanation here.
Thanks for any thoughts or advice.