Baumstruktur mit rekursiver Query auslesen

[Andrea H.]

Hallo

Ich habe nachfolgende hierarchische Datenstruktur in einer MSSQL Datenbank definiert - grundsätzlich besitzt jede "node" eine "parentnode". Weil es aber möglich ist, dass irgendwo im Baum eine Node auf eine Node an einem anderen Ort im Baum verweist, habe ich zusätzlich das Feld "referenceid" eingeführt. Wenn dieses nicht NULL ist bedeutet das, dass diese node auf eine andere Node zeigt (eine Art Querverweis innerhalb der Baumstruktur) - diese Node ist dann eigentlich nur eine Art "Hülle" und bei einer Abfrage soll der Inhalt der referenzierten Node angezeigt werden.

id | referenceid | parentid | description | node | refnode 0 | NULL | NULL | root node | / | NULL 1 | NULL | 0 | node | /1/ | NULL 2 | NULL | 1 | node | /1/1/ | NULL 3 | NULL | 0 | node | /2/ | NULL 4 | 8 | 3 | node* | /2/1/ | /4/ 5 | NULL | 0 | node | /3/ | NULL 8 | 9 | 0 | node* | /4/ | /3/1/ 9 | NULL | 5 | node | /3/1/ | NULL

Die Felder "node" und "refnode" sind vom Daten-Typ hierarchyid.

Entsprechend sollte die Abfrage dann folgenden Baum auslesen:

0           root node
+-1         node
  +-2       node
+-3         node
  +-8(4)    node* 4 die ein verweis auf 8 besitzt (die wiederum eine linknode ist)
    +-9(8)  node *8 die ein verweis auf 9 besitzt
+-4         node
+-5         node
  +-9       node
+-8         node

Für die Abfrage habe ich mir folgende Query geschrieben:

DECLARE @node as hierarchyid;
 
Select @node = node FROM nodes WHERE node.ToString() = '/'
 
;WITH cte AS (
        SELECT  
                id,
                referenceid,
                parentid, 
                description,
                node,
                refnode
        FROM nodes a 
        WHERE node.IsDescendantOf(@node) = 1
 
        UNION ALL
 
        SELECT 
                b.id,
                b.referenceid,
                b.parentid, 
                b.description,
                b.node,
                b.refnode
        FROM nodes b
        INNER JOIN cte ON b.node.GetAncestor(1) = cte.refnode OR b.node.IsDescendantOf(cte.node) = 1
) 
Select *, node.ToString() from cte

Leider liest sie mir den Baum nur teilweise aus (wenn am Verweis wieder ein Verweis hängt kommt der nicht mit). Kann mir jemand dabei helfen? Gibt es allenfalls bessere Möglichkeiten die Datenstruktur aufzusetzen?

Beste Grüsse Andrea H.