CTE

CREATE TABLE #CategoryMaster (CategoryID INT,CategoryName VARCHAR(100),CategoryParentID INT NULL) INSERT INTO #CategoryMaster values(1, 'Computer',null) INSERT INTO #CategoryMaster values(4, 'Laptop',1) -- 1 INSERT INTO #CategoryMaster values(5, 'Notebook',4) INSERT INTO #CategoryMaster values(6, 'Pad',5) --1 INSERT INTO #CategoryMaster values(9, 'MiniNoteBook',5) -- 1 INSERT INTO #CategoryMaster values(2, 'Mobile',null) INSERT INTO #CategoryMaster values(7, 'Iphone',2) --2 INSERT INTO #CategoryMaster values(8, 'Nokia',2) --1 INSERT INTO #CategoryMaster values(3, 'Books',null) --1 CREATE TABLE #ProductMaster (ProductID INT,ProductName VARCHAR(100),CategoryID INT) INSERT INTO #ProductMaster values(1, 'Iphone4',7) INSERT INTO #ProductMaster values(2, 'Lumia 800',8) INSERT INTO #ProductMaster values(3, 'Iphone5',7) INSERT INTO #ProductMaster values(4, 'HpNotebook',9) INSERT INTO #ProductMaster values(5, 'Dell',4) INSERT INTO #ProductMaster values(6, 'Hack Book',3) INSERT INTO #ProductMaster values(7, 'IPad',6); WITH CAT_CTE AS ( SELECT CategoryID, CategoryParentID, 0 AS ParentLevel FROM #CategoryMaster --WHERE CategoryParentID IS NULL UNION ALL SELECT CAT_CTE.CategoryID,c.CategoryParentID, ParentLevel + 1 AS ParentLevel FROM CAT_CTE INNER JOIN #CategoryMaster c ON CAT_CTE.CategoryParentID = c.CategoryID ) --SELECT * FROM CAT_CTE ORDER BY CategoryID, ParentLevel SELECT CM.CategoryID, COUNT(PM.ProductID) AS Cnt FROM #CategoryMaster CM LEFT OUTER JOIN CAT_CTE ON CAT_CTE.CategoryParentID = CM.CategoryID LEFT OUTER JOIN #ProductMaster AS PM ON PM.CategoryID = CAT_CTE.CategoryID GROUP BY CM.CategoryID /* SELECT * FROM #CategoryMaster SELECT * FROM #ProductMaster */ DROP TABLE #CategoryMaster DROP TABLE #ProductMaster