Lowest Child in recusive query

• edberg16

  SSC Rookie

  Points: 46

  More actions

  January 13, 2015 at 8:49 am

  #313627

  Hi,

  I have following input:

  CREATE TABLE #tree

  (

  Childid varchar(20),

  Parentid varchar(20)

  )

  INSERT INTO #tree

  (Childid,ParentId)

  SELECT '123' , null UNION ALL

  SELECT '456' , '123' UNION ALL

  SELECT '789' , '456' UNION ALL

  SELECT '870' , '456' UNION ALL

  SELECT '985' , '870';

  Input:

  Child IDParent ID

  123 NULL

  456 123

  789 456

  870 456

  985 870

  I am trying to populate lowest level child with path and depth...Output should be:

  Child IDParent IDLast ChildPath Depth

  123 NULL 789/123 1

  456 123 789/123/456 2

  789 456 789/123/456/789 3

  123 NULL 985 /123 1

  456 123 985/123/456 2

  870 456 985/123/456/870 3

  985 870 985/123/456/870/9854

  Any suggestions. Thanks

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  January 13, 2015 at 8:57 am

  #1769848

  Hi and welcome to SSC!!!

  You will need a recursive cte for this type of thing. If you can change your table to use the HierarchyID the output would be simple.

  If you need some help with this please see the first article in my signature for best practices when posting questions.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• edberg16

  SSC Rookie

  Points: 46

  More actions

  January 13, 2015 at 9:14 am

  #1769858

  Thanks Sean.

  CREATE TABLE #tree

  (

  Childid varchar(20),

  Parentid varchar(20)

  )

  INSERT INTO #tree

  (Childid,ParentId)

  SELECT '123' , null UNION ALL

  SELECT '456' , '123' UNION ALL

  SELECT '789' , '456' UNION ALL

  SELECT '870' , '456' UNION ALL

  SELECT '985' , '870';

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  January 13, 2015 at 9:35 am

  #1769863

  WITH LastChildren AS (

  SELECT d.ChildID

  FROM #tree d

  WHERE NOT EXISTS(SELECT * FROM #tree d2 WHERE d2.ParentID = d.ChildID)

  ),

  Recur AS (

  SELECT ChildID,ParentID, CAST('/' + CAST(ChildID AS VARCHAR(10)) AS VARCHAR(1000)) AS Path, 1 AS Depth

  FROM #tree

  WHERE ParentID IS NULL

  UNION ALL

  SELECT c.ChildID,c.ParentID, CAST(r.Path + '/' + CAST(c.ChildID AS VARCHAR(10)) AS VARCHAR(1000)), r.Depth+1

  FROM #tree c

  INNER JOIN Recur r ON r.ChildID = c.ParentID

  )

  SELECT r2.ChildID,r2.ParentID,l.ChildID AS LastChild,r2.Path,r2.Depth

  FROM LastChildren l

  INNER JOIN Recur r ON r.ChildID = l.ChildID

  INNER JOIN Recur r2 ON r.Path LIKE r2.Path+'%';

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• edberg16

  SSC Rookie

  Points: 46

  More actions

  January 13, 2015 at 10:15 am

  #1769872

  Thanks Mark Cowne.

  When I try to run the query, its giving following error:

  A non-recursive WITH clause or view should not reference itself within its own definition.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  January 13, 2015 at 10:23 am

  #1769878

  edberg16 (1/13/2015)

  ---

  Thanks Mark Cowne.

  When I try to run the query, its giving following error:

  A non-recursive WITH clause or view should not reference itself within its own definition.

  This works fine for me on 2008r2 and produces just what you are looking for.

  if OBJECT_ID('tempdb..#tree') is not null drop table #tree

  CREATE TABLE #tree

  (

  Childid varchar(20),

  Parentid varchar(20)

  )

  INSERT INTO #tree

  (Childid,ParentId)

  SELECT '123' , null UNION ALL

  SELECT '456' , '123' UNION ALL

  SELECT '789' , '456' UNION ALL

  SELECT '870' , '456' UNION ALL

  SELECT '985' , '870';

  WITH LastChildren AS (

  SELECT d.ChildID

  FROM #tree d

  WHERE NOT EXISTS(SELECT * FROM #tree d2 WHERE d2.ParentID = d.ChildID)

  ),

  Recur AS (

  SELECT ChildID,ParentID, CAST('/' + CAST(ChildID AS VARCHAR(10)) AS VARCHAR(1000)) AS Path, 1 AS Depth

  FROM #tree

  WHERE ParentID IS NULL

  UNION ALL

  SELECT c.ChildID,c.ParentID, CAST(r.Path + '/' + CAST(c.ChildID AS VARCHAR(10)) AS VARCHAR(1000)), r.Depth+1

  FROM #tree c

  INNER JOIN Recur r ON r.ChildID = c.ParentID

  )

  SELECT r2.ChildID,r2.ParentID,l.ChildID AS LastChild,r2.Path,r2.Depth

  FROM LastChildren l

  INNER JOIN Recur r ON r.ChildID = l.ChildID

  INNER JOIN Recur r2 ON r.Path LIKE r2.Path+'%';

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  January 13, 2015 at 10:34 am

  #1769882

  edberg16 (1/13/2015)

  ---

  Thanks Mark Cowne.

  When I try to run the query, its giving following error:

  A non-recursive WITH clause or view should not reference itself within its own definition.

  Works for me on 2012 and 2008R2, what version are you using?

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• edberg16

  SSC Rookie

  Points: 46

  More actions

  January 13, 2015 at 11:19 am

  #1769901

  Mark Cowne (1/13/2015)

  ---

  edberg16 (1/13/2015)

  ---

  Thanks Mark Cowne.

  When I try to run the query, its giving following error:

  A non-recursive WITH clause or view should not reference itself within its own definition.

  Works for me on 2012 and 2008R2, what version are you using?

  Thanks Mark. Your awesome. Its working good . Thanks once again.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  January 13, 2015 at 11:20 am

  #1769902

  edberg16 (1/13/2015)

  ---

  Mark Cowne (1/13/2015)

  ---

  edberg16 (1/13/2015)

  ---

  Thanks Mark Cowne.

  When I try to run the query, its giving following error:

  A non-recursive WITH clause or view should not reference itself within its own definition.

  Works for me on 2012 and 2008R2, what version are you using?

  Thanks Mark. Your awesome. Its working good . Thanks once again.

  You're welcome!

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• edberg16

  SSC Rookie

  Points: 46

  More actions

  January 14, 2015 at 3:36 pm

  #1770280

  Mark,

  I am trying to run the query without Path column but I end up with limited result set.

  Any suggestions.

  Thanks.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  January 14, 2015 at 3:39 pm

  #1770283

  edberg16 (1/14/2015)

  ---

  Mark,

  I am trying to run the query without Path column but I end up with limited result set.

  Any suggestions.

  Thanks.

  What does that mean? Can you share the code you are running and what you want as the desired output?

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• edberg16

  SSC Rookie

  Points: 46

  More actions

  January 14, 2015 at 4:22 pm

  #1770298

  Sean Lange (1/14/2015)

  ---

  edberg16 (1/14/2015)

  ---

  Mark,

  I am trying to run the query without Path column but I end up with limited result set.

  Any suggestions.

  Thanks.

  What does that mean? Can you share the code you are running and what you want as the desired output?

  Hi Sean,

  I want the output to be(no need of Path column)

  Child IDParent IDLast ChildDepth

  123NULL 7891

  4561237892

  7894567893

  123NULL9851

  4561239852

  8704569853

  9858709854

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  January 15, 2015 at 7:25 am

  #1770450

  edberg16 (1/14/2015)

  ---

  Sean Lange (1/14/2015)

  ---

  edberg16 (1/14/2015)

  ---

  Mark,

  I am trying to run the query without Path column but I end up with limited result set.

  Any suggestions.

  Thanks.

  What does that mean? Can you share the code you are running and what you want as the desired output?

  Hi Sean,

  I want the output to be(no need of Path column)

  Child IDParent IDLast ChildDepth

  123NULL 7891

  4561237892

  7894567893

  123NULL9851

  4561239852

  8704569853

  9858709854

  You could modify the logic so that Path isn't what is used to build this or you could just simply not include that column in your final select.

  SELECT r2.ChildID

  , r2.ParentID

  , l.ChildID AS LastChild

  --, r2.Path

  , r2.Depth

  FROM LastChildren l

  INNER JOIN Recur r ON r.ChildID = l.ChildID

  INNER JOIN Recur r2 ON r.Path LIKE r2.Path+'%';

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/