SQL Parent child tree data return only completed tree nodes from list

1

I have a parent child tree relationship in SQL Database. There are many tables holding all my data/relationships, the important data I need for this purpose is in the table below and I am using a recursive CTE query to get that. My application and database design are working perfectly for what I am doing. I need to run some management/maintenance to confirm the data is correct and what I am trying to do is outlined below:

What my data set contains is: - FilterID - This is the Parent Record ID - depth - in my CTE, this is giving me the number of records for that tree relationship. - sortCol - in my CTE I am building this out (this is just a combined binary string of all the FilterIDs) - TreeListOfFilterIDs - This is a list of all the FilterIDs in the tree for this record at this level. - RESULTS I WANT - This is a column I added to identify the list of fields I want to return from my dataset

In this example the FilterID 35 is the starting record. The parent child relathionship can have 1 parent and 2 (ore more) different paths for children, so 1 Parent can have 2 or more differnet children. NOTE: This relatinoship is intentional as 1 parent can have 2 different children and I am using that in my code/processes).

What I need to get from the below data set is to return only records for the final path of the parent/child relationship in each node. I added a column to the below data that I do not have from my query called "RESULTS I WANT" where I only want to get the records with the "X" in them.

I am populating the tables and my code/processes are working with how I have designed everything. What I am tryign to accomplish now with this logic is to find the unique final paths so I can easily identify each final path and make sure they are correct. This would be for maintenace purposes to monitor the data and the paths.

FilterID    depth   sortCol                                                 TreeListOfFilterIDs     RESULTS I WANT
35          0       0x00000023                                              35
36          1       0x0000002300000024                                      35,36
37          2       0x000000230000002400000025                              35,36,37
39          3       0x00000023000000240000002500000027                      35,36,37,39             X
38          2       0x000000230000002400000026                              35,36,38
40          3       0x00000023000000240000002600000028                      35,36,38,40
44          4       0x000000230000002400000026000000280000002C              35,36,38,40,44          X
41          3       0x00000023000000240000002600000029                      35,36,38,41
45          4       0x000000230000002400000026000000290000002D              35,36,38,41,45          X
42          3       0x0000002300000024000000260000002A                      35,36,38,42
46          4       0x0000002300000024000000260000002A0000002E              35,36,38,42,46          X
43          3       0x0000002300000024000000260000002B                      35,36,38,43
47          4       0x0000002300000024000000260000002B0000002F              35,36,38,43,47
48          5       0x0000002300000024000000260000002B0000002F00000030      35,36,38,43,47,48       X
49          5       0x0000002300000024000000260000002B0000002F00000031      35,36,38,43,47,49       X

One note, the above data is from my CTE results but is being sorted by the sortcol value (so this is not the order the data is being inserted into the CTE).

SQL to generate the above results:

-- this combines the required answers for the next questions to display (will join to what is filled out in the form
IF OBJECT_ID('tempdb..#RequiredAnswersToFindNextFiltersToDisplay') IS NOT NULL
    DROP TABLE #RequiredAnswersToFindNextFiltersToDisplay

CREATE TABLE #RequiredAnswersToFindNextFiltersToDisplay (       
    FilterID INT,           
    FormAssociationID INT,  
    RequiredAnswerFilterID INT
)


-- this gets the RequiredAnswersIntoA String for joining on
INSERT INTO #RequiredAnswersToFindNextFiltersToDisplay (
    FilterID, FormAssociationID, RequiredAnswerFilterID
)


VALUES
( 35, 1, 0 ), 
( 36, 2, 35 ), 
( 37, 3, 36 ), 
( 38, 4, 36 ), 
( 39, 5, 37 ), 
( 40, 6, 38 ), 
( 41, 7, 38 ), 
( 42, 8, 38 ), 
( 43, 9, 38 ), 
( 44, 10, 40 ), 
( 45, 11, 41 ), 
( 46, 12, 42 ), 
( 47, 13, 43 ), 
( 48, 14, 47 ), 
( 49, 15, 47 )



;WITH ItemDataCTE(FilterID, FormAssociationID, RequiredAnswerFilterID, depth, sortcol, TreeListOfFilterIDs)
AS (
  -- anchor member
  SELECT FilterID, FormAssociationID, RequiredAnswerFilterID, 0, CAST(FilterID AS VARBINARY(900)) AS SortCol,
  CAST(FilterID AS VARCHAR(MAX)) AS TreeListOfFilterIDs
  FROM #RequiredAnswersToFindNextFiltersToDisplay
  WHERE RequiredAnswerFilterID = 0

  UNION ALL

  SELECT ID.FilterID, ID.FormAssociationID, ID.RequiredAnswerFilterID, M.depth + 1, CAST(M.SortCol + CAST(ID.FilterID AS BINARY(4)) AS VARBINARY(900)) SortCol,
  CAST(M.TreeListOfFilterIDs + ',' + CAST(ID.FilterID AS VARCHAR(50)) AS VARCHAR(MAX)) AS TreeListOfFilterIDs     
  FROM #RequiredAnswersToFindNextFiltersToDisplay ID      
  INNER JOIN ItemDataCTE AS M ON ID.RequiredAnswerFilterID = M.FilterID   
)

SELECT *
FROM ItemDataCTE
ORDER BY ItemDataCTE.sortcol
sql-server
recursion
parent-child
asked on Stack Overflow Apr 18, 2018 by Brad • edited Apr 20, 2018 by Brad

1 Answer

1

The simplest way to do it is to add a self join to the recursive cte with not exists:

SELECT *
FROM ItemDataCTE as c0
WHERE NOT EXISTS
(
    SELECT 1
    FROM ItemDataCTE AS c1
    WHERE c1.TreeListOfFilterIDs LIKE c0.TreeListOfFilterIDs +',%'
)
ORDER BY sortcol

Result:

FilterID    FormAssociationID   RequiredAnswerFilterID  depth   sortcol                         TreeListOfFilterIDs
39          5                   37                      3       00035000360003700039            35,36,37,39
44          10                  40                      4       0003500036000380004000044       35,36,38,40,44
45          11                  41                      4       0003500036000380004100045       35,36,38,41,45
46          12                  42                      4       0003500036000380004200046       35,36,38,42,46
48          14                  47                      5       000350003600038000430004700048  35,36,38,43,47,48
49          15                  47                      5       000350003600038000430004700049  35,36,38,43,47,49
answered on Stack Overflow Apr 21, 2018 by Zohar Peled

User contributions licensed under CC BY-SA 3.0