- USE Northwind
- Write a SQL statement where by you join the product and order table with the above UDF and return the actual orders for the product that has the most orders. Test the function with the CategoryID of 2
SELECT od.orderid, p.ProductName, fn.count_of_Orders
from Products p JOIN dbo.fnProductsbyCategory(2) fn ON p.ProductName = fn.productName
JOIN [Order Details] od ON p.ProductID = od.ProductID
WHERE fn.count_of_Orders =(SELECT MAX(count_of_Orders) FROM dbo.fnProductsbyCategory(2))
ORDER BY p.ProductName
This problem is related to Question 17 (using the inline table UDF). For CategoryID = 2 (Condiments), the ProductName with the highest Count of Orders is returned with its related Count of Orders value. Also, the OrderIDs are returned for each order with that ProductName. The important concept to be aware of is the way the UDF is referenced in the SELECT and FROM and WHERE statements.