Writing a recursive query using Common Table Expressions

In our following example, 2 tables are linked to each other.

The product table is linked to the category table. In the category table, categories can have several levels of parent-child relations.

For examaple:
The category table can contain:

      Food -> Beverages -> Beer

           OR

      Food -> Beverages -> Sodas -> Cola

The Product table however can contain:

      Duvel -> Beer

How to get the products and their corresponding category fast, by defining the root category as a parameter?

You can do this with a recursive query using CTE (common table expresssions)

Our category table and product table:

 

 The common table expression which will return all items for category 'Food'

1: WITH  RecursiveQuery  AS 

2: ( 

3:  SELECT   c1.ID,  c1.Category  ,  CAST(  AS  NVARCHAR( 50))  AS  Parent  ,  0 AS  Level  ,  c1.ParentCategory 

4:  FROM     Category  c1 

5:  WHERE    c1.ParentCategory  IS  NULL 

6:   AND  c1.Category  =   

7:  UNION  ALL 

8:  SELECT   c2.ID,  c2.Category  ,  RecursiveQuery.Category  AS  Parent  ,  Level  +  1 AS  Level,  c2.ParentCategory 

9:  FROM     Category  c2 

10:  JOIN  RecursiveQuery  ON  RecursiveQuery.ID  =  c2.ParentCategory 

11: )

12: 

The select statement returning the products with their respective category based upon the main category 'Food'

1: SELECT   Category,  

2:   ISNULL(Products.Product  ,  )  AS  Product,  

3:   ISNULL(Products.UnitPrice,  0)  AS  UnitPrice 

4: FROM     RecursiveQuery 

5: INNER  JOIN  Products  ON  Products.CategoryID  =  RecursiveQuery.ID 

6: ORDER  BY  Level  ,Parent,  Category

7: 

See the result:

Have Fun!

SQL Server Day 2010

Thursday, December 2nd, 2010.
San Marco Village in Schelle (Antwerp)
 
Book those calendars now!