Transact SQL 101 - Lesson 11_02_SQL.txt

--Example 1
USE adventureworkslt
GO

WITH prod_cte (CatName, CatID, ParentCatID, CatLevel)
AS
(
	SELECT pc.name, pc.productcategoryid, pc.parentproductcategoryid, 0 AS CatLevel
	FROM saleslt.productcategory pc
	WHERE pc.parentproductcategoryid IS NULL
	UNION ALL
	SELECT pc.name, pc.productcategoryid, pc.parentproductcategoryid, CatLevel + 1
	FROM saleslt.productcategory pc
		INNER JOIN prod_cte cte ON pc.parentproductcategoryid = cte.CatID
)
SELECT CatName, CatID, ParentCatID, CatLevel
FROM prod_cte
ORDER BY CatLevel, ParentCatID

--Example 2
USE adventureworks
GO

WITH emp_cte (EmpID, MgrID, Title, EmpLevel)
AS
(
	SELECT e.employeeid, e.managerid, e.title, 0 AS EmpLevel
	FROM humanresources.employee e
	WHERE e.managerid IS NULL
	UNION ALL
	SELECT e.employeeid, e.managerid, e.title, EmpLevel + 1
	FROM humanresources.employee e
		INNER JOIN emp_cte c ON e.managerid = c.EmpID	
)
SELECT EmpID, MgrID, Title, EmpLevel
FROM emp_cte
ORDER BY EmpLevel, MgrID


--Example 3 (Bonus - Example of multiple recursive members.  Not Shown in Video.)
USE adventureworkslt
GO

WITH prod_cte (ProdCatID, ProdCatName, ParentCatID, ProdCatLevel)
AS
(
	SELECT pc.productcategoryid, pc.name, pc.parentproductcategoryid, 0 AS ProdCatLevel
	FROM saleslt.productcategory pc
	WHERE pc.parentproductcategoryid IS NULL
	AND pc.name = 'Bikes'
	UNION ALL
	SELECT  pc.productcategoryid, pc.name, pc.parentproductcategoryid, ProdCatLevel + 1
	FROM saleslt.productcategory pc
		INNER JOIN prod_cte cte ON pc.parentproductcategoryid = cte.ProdCatID
	UNION ALL
	SELECT p.productid, p.name, p.productcategoryid, ProdCatLevel + 1
	FROM saleslt.product p
		INNER JOIN prod_cte cte ON p.productcategoryid = cte.ProdCatID
)
SELECT ProdCatID, ProdCatName, ParentCatID, ProdCatLevel
FROM prod_cte
ORDER BY ProdCatLevel, ParentCatID