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!

Do you know that CTE's can be recursive? See here: http://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/ — Mike Cheel, Nov 11 '13 at 19:44
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. — HABO, Nov 11 '13 at 20:52
@MikeCheel in my cte example, i'm recursively calling it. Am i not? — Sam Ccp, Nov 12 '13 at 00:13
@HABO 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 — Sam Ccp, Nov 12 '13 at 00:14
@Sam So you are. For some reason I read it differently I guess. Long day. — Mike Cheel, Nov 12 '13 at 01:24

score 53 · Accepted Answer · answered Nov 11 '13 at 20:22

53

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!

answered Nov 11 '13 at 20:22

John Bustos

19,036
17
89
151

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 ? – Sam Ccp Nov 11 '13 at 23:03
2

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... – John Bustos Nov 12 '13 at 14:02
Needs a `;` before `WITH` – Null Head Sep 12 '16 at 22:57
2

@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`. – iamdave May 22 '17 at 09:27
what if 2 products having same parentid. Will this work? – Raghurocks May 25 '17 at 11:46

Charles Bretana · Answer 2 · 2013-11-11T20:45:00.963

3

;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

edited Nov 11 '13 at 20:45

answered Nov 11 '13 at 19:45

Charles Bretana

143,358
22
150
216

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. – Sam Ccp Nov 11 '13 at 19:57
What do you mean *based on the parent -> child relation* ? If not incrementing value by one for each parent-Child connection ? – Charles Bretana Nov 11 '13 at 19:59
on the example, the levels column for the productid 311 is 311, 1174, that's the relationship, maybe i'm explaining it wrong? – Sam Ccp Nov 11 '13 at 20:01
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 – Charles Bretana Nov 11 '13 at 20:37
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. – Sam Ccp Nov 11 '13 at 20:54

CTE to get all children (descendants) of a parent

2 Answers2

Linked

Related