SQL: Recursively get parent records using Common Table Expressions

Question

Suppose you have to following tables where a sale consists of products and a product can be placed in multiple categories. Whereby categories have a hierarchy structure like:

Man
 Shoes
  Sport
  Casual
 Watches
Women
 Shoes
  Sport
  Casual
 Watches

Tables:

Sale:
    id name 
    1  Sale1

Product:
    id saleidfk name 
    1  1        a
    2  1        b
    3  1        c
    4  1        d
    5  1        e

ProductCategory :
    productid categoryid 
    1         3
    2         3           
    3         4
    4         5
    5         10     

Category:
    id ParentCategoryIdFk name 
    1  null               Men
    2  1                  Shoes
    3  2                  Sport
    4  2                  Casual
    5  1                  Watches
    6  null               Women
    7  6                  Shoes
    8  7                  Sport
    9  7                  Casual
    10 6                 Watches

Question:

Now on my website I want to create a control where only the categories are shown of a certain sale and where the categories are filled with the products of the sale. I also want to include the hierarchy structure of the categories. So if we have a leaf node, recursively go up to the top node.

So with sale1 I should have a query with the following result:

Men
  Shoes
    Sport
    Casual
  Watches
Women
  Watches

Answer 1

Try something like this - the basic CTE to get a hierarchical listing of your categories would be similar to this:

WITH Categories AS
(
    SELECT Cat.ID, Cat.NAME, Cat.ParentCategoryID, CAST('none' AS VARCHAR(50)) AS 'ParentCategory', 1 AS 'Level'
    FROM dbo.MBCategory Cat
    WHERE Cat.ParentCategoryID IS NULL

    UNION ALL

    SELECT Cat.ID, Cat.NAME, Cat.ParentCategoryID, c2.NAME AS 'ParentCategory', LEVEL + 1
    FROM dbo.MBCategory CAT
    INNER JOIN Categories c2 ON cat.ParentCategoryID = c2.ID
)
SELECT * FROM Categories

Now what you need to do is join your other tables to this CTE, to get the following query in the end:

WITH Categories AS
(
    SELECT Cat.ID, Cat.NAME, Cat.ParentCategoryID, CAST('none' AS VARCHAR(50)) AS 'ParentCategory', 1 AS 'Level'
    FROM dbo.MBCategory Cat
    WHERE Cat.ParentCategoryID IS NULL

    UNION ALL

    SELECT Cat.ID, Cat.NAME, Cat.ParentCategoryID, c2.NAME AS 'ParentCategory', LEVEL + 1
    FROM dbo.MBCategory CAT
    INNER JOIN Categories c2 ON cat.ParentCategoryID = c2.ID
)
SELECT DISTINCT s.*, c.*
FROM dbo.Sale s
INNER JOIN dbo.Product p ON p.SaleID = s.ID
INNER JOIN dbo.ProductCategory pc ON p.ID = pc.ProductID
INNER JOIN Categories c ON pc.CategoryID = c.ID
ORDER BY Level

This gives me a resulting output something like:

ID  Name   CatID  CatName  ParentCatID  ParentCatName Level
 1  Sale1    5    Watches      1            Men         2
 1  Sale1   10    Watches      6            Women       2 
 1  Sale1    3    Sport        2            Shoes       3
 1  Sale1    3    Sport        2            Shoes       3
 1  Sale1    4    Casual       2            Shoes       3