Rekursive CTE-Prüfung, ob Datensatz auf irgendeiner Ebene vorhanden ist, dann alle untergeordneten Elemente abrufen (Ordnerstruktur)

Ich versuche mit einem rekursiven CTE zu überprüfen, ob ein Benutzer Zugriff auf einen Ordner hat, und wenn ja, dann auf den Ordner und alle untergeordneten Elemente

Die Struktur ist grundlegend

CREATE TABLE [dbo].[Folders](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](1024) NOT NULL,
 [ParentId] [int] NULL)

Dies ist die Tabelle, die einen Benutzer mit einem Ordner verknüpft

CREATE TABLE [dbo].[FolderAccess](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [UserId] [bigint] NOT NULL,
 [FolderId] [int] NOT NULL)

Anhand der folgenden Beispieldaten

SELECT * FROM Folders

Ausweis	Eltern ID	Name
ein	NULL	Wurzel
2	ein	Unterordner 1
3	2	Unterordner 2
4	3	Unterordner 3
5	4	Unterordner 4
6	5	Unterordner 5

Lösung des Problems

Die folgende Abfrage versucht zuerst, die Ordner aufzulisten, auf die der Benutzer Zugriff hat, und dann die erforderliche Hierarchie unter den zugänglichen Ordnern

DECLARE @UserId INT = 1;
DECLARE @FolderId INT = 5;
WITH
AccessibleHierarchy (Id, ParentId, Name) AS
(
 SELECT fold.Id, fold.ParentId, fold.Name FROM Folders fold 
 WHERE fold.[CreatorUserId] = @UserId or fold.ID in (select [FolderId] from [FolderAccess] where [UserId] = @UserId)
 UNION ALL
 SELECT parents.Id, parents.ParentId, parents.Name FROM Folders parents 
 INNER JOIN AccessibleHierarchy cte on cte.Id = parents.ParentId
),
QueriedHierarchy (Id, ParentId, Name) AS
(
 SELECT distinct fold.Id, fold.ParentId, fold.Name FROM AccessibleHierarchy fold 
 WHERE fold.ID = @FolderId
 UNION ALL
 SELECT parents.Id, parents.ParentId, parents.Name FROM Folders parents 
 INNER JOIN QueriedHierarchy cte on cte.Id = parents.ParentId
)
select * from QueriedHierarchy

db<>Geige

Bearbeiten: Verwendung einer CreatorUserId-Spalte in Ordnern hinzugefügt und ein eindeutiges Schlüsselwort in QueriedHierarchy hinzugefügt, falls dort eine Hierarchie durch mehrere CreatorUserId/FolderAccess zulässig ist