INSERT #TempTreeStructures (TreeStructureId) VALUES (@RootTreeStructureId)
 
SET @RootRightValue = @RootRightValue - 1
 
SELECT @RightValue = RightValue,
@TreeStructureId = TreeStructureId
FROM TreeStructures
WHERE TreeId = @TreeId AND
LeftValue = @RootLeftValue + 1
 
WHILE (@RightValue < @RootRightValue)
BEGIN
INSERT #TempTreeStructures (TreeStructureId) VALUES (@TreeStructureId)
SELECT @RightValue = RightValue,
@TreeStructureId = TreeStructureId
FROM TreeStructures
WHERE TreeId = @TreeId AND
LeftValue = @RightValue + 1
END
 
INSERT #TempTreeStructures (TreeStructureId) VALUES (@TreeStructureId)
SELECT ts.TreeStructureId,
ts.LeftValue,
ts.RightValue,
tn.Name,
tn.TYPE,
tn.DATA ,
tn.Description,
tn.DateChanged ,
tn.UserId
FROM TreeStructures ts
JOIN TreeNodes tn ON ts.TreeStructureId = tn.TreeStructureId
WHERE ts.TreeStructureId = (SELECT TOP 1 TreeStructureId FROM #TempTreeStructures)
AND tn.TreeNodeId = (SELECT TOP 1 tn2.TreeNodeId FROM TreeNodes tn2 WHERE tn2.TreeStructureId = ts.TreeStructureId ORDER BY tn2.DateChanged DESC)
UNION ALL
SELECT ts.TreeStructureId,
ts.LeftValue,
ts.RightValue,
tn.Name,
tn.TYPE,
tn.DATA ,
NULL AS [Description],
NULL AS DateChanged,
tn.UserId
FROM TreeStructures ts
JOIN TreeNodes tn ON ts.TreeStructureId = tn.TreeStructureId
WHERE ts.TreeStructureId IN (SELECT TreeStructureId FROM #TempTreeStructures) AND
ts.TreeStructureId <> (SELECT TOP 1 TreeStructureId FROM #TempTreeStructures) AND
tn.TreeNodeId = (SELECT TOP 1 tn2.TreeNodeId FROM TreeNodes tn2 WHERE tn2.TreeStructureId = ts.TreeStructureId ORDER BY tn2.DateChanged DESC)

;WITH TreeData (TreeStructureId, LeftValue, RightValue, TreeId)
AS (
SELECT TOP 1
TreeStructures.TreeStructureId
, TreeStructures.LeftValue
, TreeStructures.RightValue
, TreeStructures.TreeId
FROM TreeStructures
WHERE TreeStructures.TreeId = @TreeId
AND TreeStructures.LeftValue = @RootLeft + 1
UNION ALL
SELECT
TreeStructures.TreeStructureId
, TreeStructures.LeftValue
, TreeStructures.RightValue
, TreeStructures.TreeId
FROM TreeStructures
INNER JOIN TreeData ON TreeStructures.LeftValue = TreeData.RightValue + 1
WHERE TreeStructures.TreeId = TreeData.TreeId
)
SELECT TreeStructures.TreeStructureId,
TreeStructures.LeftValue,
TreeStructures.RightValue,
TreeNodes.Name,
TreeNodes.TYPE,
TreeNodes.DATA ,
TreeNodes.Description,
TreeNodes.DateChanged ,
TreeNodes.UserId
FROM TreeStructures
JOIN TreeNodes ON TreeNodes.TreeStructureId = TreeStructures.TreeStructureId
WHERE TreeNodes.TreeNodeId = (
SELECT TOP 1 tn2.TreeNodeId
FROM TreeNodes tn2
WHERE TreeStructureId = TreeStructures.TreeStructureId
ORDER BY DateChanged DESC
)
AND TreeStructures.TreeStructureId = @RootTreeStructureId
UNION ALL
SELECT TreeData.TreeStructureId,
TreeData.LeftValue,
TreeData.RightValue,
TreeNodes.Name,
TreeNodes.TYPE,
TreeNodes.DATA,
TreeNodes.Description,
TreeNodes.DateChanged ,
TreeNodes.UserId
FROM TreeData
JOIN TreeNodes ON TreeNodes.TreeStructureId = TreeData.TreeStructureId
WHERE TreeNodes.TreeNodeId = (
SELECT TOP 1 tn2.TreeNodeId
FROM TreeNodes tn2
WHERE TreeStructureId = TreeData.TreeStructureId
ORDER BY DateChanged DESC
)
ORDER BY LeftValue
;