SQL Question 18

Problem

  • 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

Code

USE Northwind
GO
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

Result

Question 18 Result

Question 18 Result

Notes

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.
 

No Comments Yet.

Leave a comment