Write a SELECT statement that returns two columns: VendorName and LargestInv (LargestInv is the correlation name of the subquery)
Subquery portion: SELECT statement that returns the largest InvoiceTotal from the Invoices table (you will need to perform the JOIN within the subquery in one of the clauses).
Sort the results by LargestInv from largest to smallest.(Subquery Must be in the Select statement)
I have tried this but My subqueries returning more than one value
USE AP
SELECT VendorName, (SELECT MAX(InvoiceTotal) FROM Invoices JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
GROUP BY Invoices.VendorID) AS LargestInv
FROM Vendors
Your issue is scope.
The sub-query shouldn't be joining to the
Vendor
table if the goal is a correlated sub-query. The "correlated" part comes from joining the results of the inner query (the sub-query) to the outer query.As written, you're finding VendorID inside the sub-query and the results aren't correlated to the outer query at all. Hence your error message.
Edit (extended explanation):
A correlated sub-query isn't designed to pull up a full result set, like the sub-query you were writing at first. It's designed to go over to another table and use a value (or values) from the outer query to bring back a single result, one row at a time.
In this case, using the VendorID from the Vendors table, go over to Invoices, calculate a MAX value "WHERE" the VendorID in Invoices matches the VendorID ON THIS ROW, bring that single value back, then, next row, go back and do that again. And again and again.
It's one way to get the data, but it's not usually efficient. Later, though, you'll learn to use correlated sub-queries in
(NOT) EXISTS
clauses, and in that context they tend to be extremely efficient. Story for another day, but it's one reason the construct is important to know.So, your way was good, because it was set based and would tend to be more efficient as a sub-query in the
FROM
clause, but this way, row by row, is important to understand conceptually.