Finding last-used multi-self-reference-table JOIN'd Columns

Transact-SQL
1

I've got a self referencing table. Its upside down, so the first level gives me the Child, then the next the Parent, and the one after that the Grand Parent, and so on.

There are varying numbers of ancestor levels, but I want the earliest of those first - i.e. the last table that is joined successfully is first, and the first one joined is last.

There are never any more than 7 levels, and what I have done must be incredibly cumbersome and slow!, is there a better way?

Basically its a table of Folders and Sub-Folders. I want the ID and NAME of the folder at each level.

DROP TABLE #CASES
GO
DROP TABLE #FOLDERS
GO
SELECT *
INTO	#CASES
FROM
(
	  SELECT  [CaseID]='A1234', [FLDR_ID]=146282.0
UNION ALL SELECT  'B5678', 146293.0
UNION ALL SELECT  'B5678AAA', 146303.0
UNION ALL SELECT  'B5678BBB', 146310.0
UNION ALL SELECT  'B5678CCC', 146318.0
UNION ALL SELECT  'B5678DDD', 146325.0
UNION ALL SELECT  'B5678EEE', 146332.0
UNION ALL SELECT  'B5678FFF', 146339.0
UNION ALL SELECT  'B5678GGG', 146346.0
UNION ALL SELECT  'C9876AAA', 146479.0
) AS X

SELECT *
INTO	#FOLDERS
FROM
(
	  SELECT  [FLDR_ID]=146282.0 ,    [FLDR_ParentID]=2563640.0 , [FLDR_Name]='eDocs'
UNION ALL SELECT  2563640.0,    3.0      , 'My Client Ltd (CLIENT1)'
UNION ALL SELECT  3.0      ,    1.0      , '_Inactive Client List'
UNION ALL SELECT  1.0      ,    NULL     , 'Cases'

UNION ALL SELECT  146293.0 ,    146292.0 , 'eDocs'

UNION ALL SELECT  146292.0 ,    2563658.0, 'CLIENT1.B5678 - Mgmnt General'
UNION ALL SELECT  2563658.0,    16.0     , 'My Client Ltd (CLIENT1)'

UNION ALL SELECT  16.0     ,    1.0      , '_Live Client List'

UNION ALL SELECT  146303.0 ,    146302.0 , 'eDocs'
UNION ALL SELECT  146302.0 ,    2563873.0, 'CLIENT1.B5678AAA - Mgmnt AAA'
UNION ALL SELECT  2563873.0,    2563658.0, '__CLIENT1.B5678 - Mgmnt General'

UNION ALL SELECT  146310.0 ,    146309.0 , 'eDocs'
UNION ALL SELECT  146309.0 ,    2563873.0, 'CLIENT1.B5678BBB - Mgmnt BBB'

UNION ALL SELECT  146318.0 ,    146317.0 , 'eDocs'
UNION ALL SELECT  146317.0 ,    2563873.0, 'CLIENT1.B5678CCC - Mgmnt CCC'

UNION ALL SELECT  146325.0 ,    146324.0 , 'eDocs'
UNION ALL SELECT  146324.0 ,    2563873.0, 'CLIENT1.B5678DDD - Mgmnt DDD'

UNION ALL SELECT  146332.0 ,    146331.0 , 'eDocs'
UNION ALL SELECT  146331.0 ,    2563873.0, 'CLIENT1.B5678EEE - Mgmnt EEE'

UNION ALL SELECT  146339.0 ,    146338.0 , 'eDocs'
UNION ALL SELECT  146338.0 ,    2563873.0, 'CLIENT1.B5678FFF - Mgmnt FFF'

UNION ALL SELECT  146346.0 ,    146345.0 , 'eDocs'
UNION ALL SELECT  146345.0 ,    2563873.0, 'CLIENT1.B5678GGG - Mgmnt AAA'

UNION ALL SELECT  146479.0 ,    146478.0 , 'eDocs'
UNION ALL SELECT  146478.0 ,    2563745.0, 'CLIENT2.C9876AAA - Mgmnt AAA'
UNION ALL SELECT  2563745.0,    2563744.0, '__CLIENT2.C9876 - Mgmnt General'
UNION ALL SELECT  2563744.0,    16.0     , 'Joe Bloggs (CLIENT2)'
) AS X


SELECT	C.*, X1.[FirstField], X2.*
FROM	#CASES AS C
	LEFT OUTER JOIN #FOLDERS AS P1
		 ON P1.FLDR_ID = C.FLDR_ID
	LEFT OUTER JOIN #FOLDERS AS P2
		 ON P2.FLDR_ID = P1.FLDR_ParentID
	LEFT OUTER JOIN #FOLDERS AS P3
		 ON P3.FLDR_ID = P2.FLDR_ParentID
	LEFT OUTER JOIN #FOLDERS AS P4
		 ON P4.FLDR_ID = P3.FLDR_ParentID
	LEFT OUTER JOIN #FOLDERS AS P5
		 ON P5.FLDR_ID = P4.FLDR_ParentID
	LEFT OUTER JOIN #FOLDERS AS P6
		 ON P6.FLDR_ID = P5.FLDR_ParentID
	LEFT OUTER JOIN #FOLDERS AS P7
		 ON P7.FLDR_ID = P6.FLDR_ParentID
	CROSS APPLY
	(
		SELECT	[FirstField] = CASE WHEN P7.FLDR_ID IS NOT NULL THEN 7
			WHEN P6.FLDR_ID IS NOT NULL THEN 6
			WHEN P5.FLDR_ID IS NOT NULL THEN 5
			WHEN P4.FLDR_ID IS NOT NULL THEN 4
			WHEN P3.FLDR_ID IS NOT NULL THEN 3
			WHEN P2.FLDR_ID IS NOT NULL THEN 2
			ELSE 1
			END
	) AS X1
	CROSS APPLY
	(
		SELECT	[L1_FLDR_ID] = CASE FirstField
					WHEN 7 THEN P7.FLDR_ID
					WHEN 6 THEN P6.FLDR_ID
					WHEN 5 THEN P5.FLDR_ID
					WHEN 4 THEN P4.FLDR_ID
					WHEN 3 THEN P3.FLDR_ID
					WHEN 2 THEN P2.FLDR_ID
					WHEN 1 THEN P1.FLDR_ID
					ELSE NULL
					END
			, [L1_FLDR_Name] = CASE FirstField
					WHEN 7 THEN P7.FLDR_Name
					WHEN 6 THEN P6.FLDR_Name
					WHEN 5 THEN P5.FLDR_Name
					WHEN 4 THEN P4.FLDR_Name
					WHEN 3 THEN P3.FLDR_Name
					WHEN 2 THEN P2.FLDR_Name
					WHEN 1 THEN P1.FLDR_Name
					ELSE NULL
					END
			, [L2_FLDR_ID] = CASE FirstField
					WHEN 7 THEN P6.FLDR_ID
					WHEN 6 THEN P5.FLDR_ID
					WHEN 5 THEN P4.FLDR_ID
					WHEN 4 THEN P3.FLDR_ID
					WHEN 3 THEN P2.FLDR_ID
					WHEN 2 THEN P1.FLDR_ID
					ELSE NULL
					END
			, [L2_FLDR_Name] = CASE FirstField
					WHEN 7 THEN P6.FLDR_Name
					WHEN 6 THEN P5.FLDR_Name
					WHEN 5 THEN P4.FLDR_Name
					WHEN 4 THEN P3.FLDR_Name
					WHEN 3 THEN P2.FLDR_Name
					WHEN 2 THEN P1.FLDR_Name
					ELSE NULL
					END
			, [L3_FLDR_ID] = CASE FirstField
					WHEN 7 THEN P5.FLDR_ID
					WHEN 6 THEN P4.FLDR_ID
					WHEN 5 THEN P3.FLDR_ID
					WHEN 4 THEN P2.FLDR_ID
					WHEN 3 THEN P1.FLDR_ID
					ELSE NULL
					END
			, [L3_FLDR_Name] = CASE FirstField
					WHEN 7 THEN P5.FLDR_Name
					WHEN 6 THEN P4.FLDR_Name
					WHEN 5 THEN P3.FLDR_Name
					WHEN 4 THEN P2.FLDR_Name
					WHEN 3 THEN P1.FLDR_Name
					ELSE NULL
					END
			, [L4_FLDR_ID] = CASE FirstField
					WHEN 7 THEN P4.FLDR_ID
					WHEN 6 THEN P3.FLDR_ID
					WHEN 5 THEN P2.FLDR_ID
					WHEN 4 THEN P1.FLDR_ID
					ELSE NULL
					END
			, [L4_FLDR_Name] = CASE FirstField
					WHEN 7 THEN P4.FLDR_Name
					WHEN 6 THEN P3.FLDR_Name
					WHEN 5 THEN P2.FLDR_Name
					WHEN 4 THEN P1.FLDR_Name
					ELSE NULL
					END
			, [L5_FLDR_ID] = CASE FirstField
					WHEN 7 THEN P3.FLDR_ID
					WHEN 6 THEN P2.FLDR_ID
					WHEN 5 THEN P1.FLDR_ID
					ELSE NULL
					END
			, [L5_FLDR_Name] = CASE FirstField
					WHEN 7 THEN P3.FLDR_Name
					WHEN 6 THEN P2.FLDR_Name
					WHEN 5 THEN P1.FLDR_Name
					ELSE NULL
					END
			, [L6_FLDR_ID] = CASE FirstField
					WHEN 7 THEN P2.FLDR_ID
					WHEN 6 THEN P1.FLDR_ID
					ELSE NULL
					END
			, [L6_FLDR_Name] = CASE FirstField
					WHEN 7 THEN P2.FLDR_Name
					WHEN 6 THEN P1.FLDR_Name
					ELSE NULL
					END
			, [L7_FLDR_ID] = CASE FirstField
					WHEN 7 THEN P1.FLDR_ID
					ELSE NULL
					END
			, [L7_FLDR_Name] = CASE FirstField
					WHEN 7 THEN P1.FLDR_Name
					ELSE NULL
					END
	) AS X2
2

The following is only a partial reply. The recursive CTE you want would be very much like the first CTE in the query below. Then, I got confused as to how you pivot those.

;WITH cte AS
(
	SELECT
		c.CaseId,
		CAST( f.FLDR_ParentID AS VARCHAR(32)) Parent,
		CAST(c.FLDR_ID AS  VARCHAR(32)) FLDR_ID,
		cast(f.FLDR_Name AS varchar(32)) AS [Name],
		1 AS Level
	FROM
		#CASES c
		INNER JOIN #FOLDERS f ON
			f.FLDR_ID = c.FLDR_ID

	UNION ALL
	
	SELECT
		ct.CaseId,
		CAST(f.FLDR_ParentID AS VARCHAR(32)),
		CAST(ct.Parent AS VARCHAR(32)),
		cast(f.FLDR_Name AS varchar(32)),
		Level+1
	FROM
		cte ct
		INNER JOIN #FOLDERS f ON
			f.FLDR_ID = ct.Parent
),
cte2 AS (
	SELECT *, COUNT(*) OVER (PARTITION BY CaseId) AS Depth FROM cte
)
SELECT
	caseId,
	MAX(CASE WHEN Depth-Level = 0 THEN FLDR_ID END) AS  FLDR_ID,
	MAX(CASE WHEN Parent IS NULL THEN Name END) AS  FirstField,
	MAX(CASE WHEN Depth-Level = 1 THEN FLDR_ID END) AS  FLDR_ID,
	MAX(CASE WHEN Depth-Level = 1 THEN Parent END) AS  FirstField,
	MAX(CASE WHEN Depth-Level = 1 THEN Name END) AS  L1_FLDR_Name,
	MAX(CASE WHEN Depth-Level = 2 THEN FLDR_ID END) AS  L2_FLDR_ID,
	MAX(CASE WHEN Depth-Level = 2 THEN Name END) AS  L2_FLDR_Name,
	MAX(CASE WHEN Depth-Level = 3 THEN FLDR_ID END) AS  L3_FLDR_ID,
	MAX(CASE WHEN Depth-Level = 3 THEN Name END) AS  L3_FLDR_Name,
	MAX(CASE WHEN Depth-Level = 4 THEN FLDR_ID END) AS  L3_FLDR_ID,
	MAX(CASE WHEN Depth-Level = 4 THEN Name END) AS  L3_FLDR_Name,
	MAX(CASE WHEN Depth-Level = 5 THEN FLDR_ID END) AS  L3_FLDR_ID,
	MAX(CASE WHEN Depth-Level = 5 THEN Name END) AS  L3_FLDR_Name,
	MAX(CASE WHEN Depth-Level = 6 THEN FLDR_ID END) AS  L3_FLDR_ID,
	MAX(CASE WHEN Depth-Level = 6 THEN Name END) AS  L3_FLDR_Name

FROM
	cte2
GROUP BY 
	caseId;
3

I didn't start! because I assumed that I either want Recursive cte OR Pivot :slight_smile: but a Recursive CTE that also Pivots would likely be faster than my umteen-JOINs followed by really-really-slow CROSS APPLY ...

Thanks, I'll take a look at you suggestion

4

P.S. My test case, on only 20,000 top-level rows, took 8 minutes to run with my mega-mega-CROSS APPLY ... so will be interesting to see the improvement