CTE to get all children (descendants) of a parent

Question

I've this problem that's giving me a headache...

Say, i've got a table with some thousands of rows and the structure of the table consists on a parent -> child relationship.

The relationships can go up to 6 levels. Here's a sample of the table structure:

ProductId | ParentId | Levels
1174           0        1174
311           1174      311, 1174
1186          311       1186, 311, 1174
448           1186      448, 1186, 311, 1174
3365          448       3365, 448, 1186, 311, 1174

We got a process that loops through the entire table to get the relationships and saves the "levels" column, this process is really slow ( because of the loops ) and i've tried with some cte to get the relationships but failed miserabily.

So far i've tried this cte but it doesn't do what i was hoping for and also, it seems to be replicating rows...

;With Parents(ProductId, ParentId, Levels)
As(
  Select ProductId, ParentId, Levels
  From Products
  Where ParentId = 0 
  Union All
  Select p.ProductId, p.ParentId, p.Levels
  From Products p
  Join Parents cte On cte.ProductId = p.ParentId
)
Select *
From Parents

As i mentioned early, we got a process that loops the table, it does its job but it can take up to 30 minutes, my question is is there a better way to do this? i know that CTE allows me to do it but i suck at it, also, the levels column should be calculated and updated on the table, is it possible?

Here's a Sqlfiddle in case someone could help, thanks!

Weeks

Do you know that CTE's can be recursive? See here: blog.sqlauthority.com/2008/07/28/… – Fireeater

Tip: A common design involves making ParentId nullable and using NULL to represent a row with no parent. It avoids using a magic number to indicate top level rows or the confusion of making a top level row its own parent, i.e. ProductId 1174 with ParentId 1174. – Trampoline

@MikeCheel in my cte example, i'm recursively calling it. Am i not? – Weeks

@Trampoline i know it's not the prettiest design, but it's a legacy system seldom used, i got the winner ticket to give some maintenance to it and i'm trying to update all of the DB stuff into Stored Procedures, this process i'm referring it's an asp file with tons and tons of selects, update, delete and insert statements. Thanks for the help :D – Weeks

@Sam So you are. For some reason I read it differently I guess. Long day. – Fireeater

Answer 1

This should do it:

WITH MyTest as
(
  SELECT P.ProductID, P.ParentID, CAST(P.ProductID AS VarChar(Max)) as Level
  FROM Products P
  WHERE P.ParentID = 0

  UNION ALL

  SELECT P1.ProductID, P1.ParentID, CAST(P1.ProductID AS VarChar(Max)) + ', ' + M.Level
  FROM Products P1  
  INNER JOIN MyTest M
  ON M.ProductID = P1.ParentID
 )
SELECT * From MyTest

And here's the updated SQL Fiddle.

Also, check out this link for help with CTEs... They're definitely good to know:

Hope this does the trick!

Apparition

Hells Yeah! this indeed did the trick, one question though, why did my attempt failed so hard when it's not that different from yours, except from the formatting of the levels column ? – Weeks

You inserted Levels into your CTE and then were simply querying it out.... It killed the idea of what you were trying to accomplish. If you look at my SQLFiddle, the original table didn't even have a Levels column - We create it based upon the product / parent columns as would be needed... – Apparition

Needs a ; before WITH – Fabulist

@NullHead No, with just needs to be the first keyword in the statement. If you were properly terminating your previous statements with semi colons you would not need to prepend one to your with. – Eurystheus

what if 2 products having same parentid. Will this work? – Patrickpatrilateral

Answer 2

;With Parents(ProductId, ParentId, Level, levels)
As(
  Select ProductId, ParentId, 0, 
     cast(ltrim(str(productId,8,0)) as varchar(max))
  From Products
  Where ParentId = 0 
  Union All
  Select p.ProductId, p.ParentId, 
      par.Level + 1,
      cast( levels + ', ' + ltrim(str(productId,8,0)) as varchar(max))
  From Products p
     Join Parents par
        On par.ProductId = p.ParentId
  )
  Select * From Parents
  Order By Level

Maje

thanks for your help, but this approach is not getting the levels correctly, its just an incrementing value. The idea is to get the levels column based on the parent -> child relation. – Weeks

What do you mean based on the parent -> child relation ? If not incrementing value by one for each parent-Child connection ? – Maje

on the example, the levels column for the productid 311 is 311, 1174, that's the relationship, maybe i'm explaining it wrong? – Weeks

or are the levels just the productIds of the items in each level as you go up the heiarchy? if so, try edited version above – Maje

In this case, represents products and their relations, for example, the product 311 can have a parent 1174 but also a 2080 parent, maybe to better explain it, this table holds not just a catalog of products, but also all of the "ingredients" of a recipe, for example a tomato salad has an id 3000 and all of its contents will be on the levels column, allowing you to simply query this column and get all the ingredients of the recipe. – Weeks