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
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
User contributions licensed under CC BY-SA 3.0