"usp_CustomerAlsoBought" Stored Procedure

Description:

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).