This is the most interesting and involved of the Customer stored procedures. It accepts a ProductID as an input. First, in the inner nested SELECT statement, it builds a set of all orders containing the ProductID. Then of those records, it returns the top 5 distinct products in those orders. This stored procedure give the Adventure Works Cycles application the "Customers who bought this item also bought" functionality.
Definition:CREATE PROCEDURE usp_CustomerAlsoBought ( @ProductID int ) AS /* We want to take the top 5 products contained in the orders where someone has purchased the given Product */ SELECT TOP 5 SOD.ProductID, P.[Name], SUM(SOD.OrderQty) as TotalNum FROM Sales.SalesOrderDetail AS SOD INNER JOIN Production.Product AS P ON SOD.ProductID = P.ProductID WHERE SOD.SalesOrderID IN ( /* This inner query should retrieve all orders that have contained the productID */ SELECT DISTINCT SalesOrderID FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID ) AND SOD.ProductID != @ProductID GROUP BY SOD.ProductID, P.[Name] ORDER BY TotalNum DESC;Database Tables Used:
Product: The Product table contains the core information about all of the items for sale on the Adventure Works Cycles web site. Its primary key is the ProductID identity field.
SalesOrderDetail: The SalesOrderDetail table contains detailed product information for each order in the system. For each product in an order, an entry is made into the SalesOrderDetail table containing the ProductID, Quantity and current UnitCost of the item. There is a many to one relationship between SalesOrderDetail table and the SalesOrderHeader table. Note the primary key in the table is both SalesOrderID and LineNumber (since those two columns are the minimum required to guarantee uniqueness of a record).