I had a requirement that called for a asp:DataGrid being filled in with product categories and a product count for each category. The way I chose to do that which is illustrated in example below was by using a subquery in the SELECT statement with a LEFT OUTER JOIN.
Please note: Subqueries are always enclosed in parentheses.
-- =============================================
-- Author: Duane Urban
-- Create date: 08/15/2009
-- Description: Gets all the product categories for a datagrid
-- =============================================
CREATE PROCEDURE [dbo].[PD_AdminGetProductCategoriesForGrid]
AS BEGIN SET NOCOUNT ON
SELECT PD_StoreProductCategories.CategoryID AS Value,
PD_StoreProductCategories.CategoryName AS [Key],
CASE PD_StoreProductCategories.Active
WHEN '0' THEN 'No'
WHEN '1' THEN 'Yes'
END AS Visible,
-- The subquey to get the product count
(SELECT COUNT(ProductCategory) AS Expr1
FROM PD_StoreProducts WITH(NOLOCK)
WHERE (PD_StoreProductCategories.CategoryID = ProductCategory))
AS ProductCount
FROM PD_StoreProductCategories WITH(NOLOCK)
LEFT OUTER JOIN PD_StoreProducts AS PD_StoreProducts_1 WITH(NOLOCK)
ON PD_StoreProductCategories.CategoryID = PD_StoreProducts_1.ProductCategory
ORDER BY [Key]
END
Subscribe