NESTED TREE (вспомнила)

category_tree_view.sql

SELECT node.Id, node.Title, node.LeftIndex, node.RightIndex, node.MetaTitle, node.MetaKeywords, node.MetaDescription, node.Cover, (COUNT(parent.Title) - 1) AS depth 
                    FROM {0}.Categories AS node, {0}.Categories AS parent 
                    WHERE node.LeftIndex BETWEEN parent.LeftIndex AND parent.RightIndex 
                    GROUP BY node.Id, node.Title, node.LeftIndex, node.RightIndex, node.MetaTitle, node.MetaKeywords, node.MetaDescription, node.Cover
                    ORDER BY node.LeftIndex

get_category_for_child.sql

SELECT node.Id, node.Title, node.LeftIndex, node.RightIndex, node.MetaTitle, node.MetaKeywords, node.MetaDescription, node.Cover, (COUNT(parent.Title) - (sub_tree.depth + 1)) AS depth
FROM {0}.Categories AS node,
	{0}.Categories AS parent,
	{0}.Categories AS sub_parent,
	(
		SELECT node.Id, node.Title, node.LeftIndex, node.RightIndex, node.MetaTitle, node.MetaKeywords, node.MetaDescription, node.Cover, (COUNT(parent.Title) - 1) AS depth
		FROM {0}.Categories AS node,
		{0}.Categories AS parent
		WHERE node.LeftIndex BETWEEN parent.LeftIndex AND parent.RightIndex
		AND node.Id = {1}
		GROUP BY node.Id, node.Title, node.LeftIndex, node.RightIndex, node.MetaTitle, node.MetaKeywords, node.MetaDescription, node.Cover
	) AS sub_tree
WHERE node.LeftIndex BETWEEN parent.LeftIndex AND parent.RightIndex
	AND node.LeftIndex BETWEEN sub_parent.LeftIndex AND sub_parent.RightIndex
	AND sub_parent.Title = sub_tree.Title
GROUP BY node.Id, node.Title, node.LeftIndex, node.RightIndex, node.MetaTitle, node.MetaKeywords, node.MetaDescription, node.Cover, sub_tree.depth
ORDER BY node.LeftIndex

get_parent_category.sql

SELECT (SELECT TOP (1) parent.Id
           FROM Category AS parent 
           WHERE parent.LeftIndex < child.LeftIndex AND parent.RightIndex > child.RightIndex    
           ORDER BY parent.RightIndex-child.RightIndex ASC) AS ParentId
FROM Category AS child
WHERE child.Id = {0}
ORDER BY child.RightIndex-child.LeftIndex DESC

get_single_node_with_depth.sql

SELECT node.Id, node.Title, node.LeftIndex, node.RightIndex, node.MetaTitle, node.MetaKeywords, node.MetaDescription, node.Cover, (COUNT(parent.Title) - 1) AS depth 
                    FROM {0}.Categories AS node, {0}.Categories AS parent 
                    WHERE (node.LeftIndex BETWEEN parent.LeftIndex AND parent.RightIndex) AND node.Id = {1}
                    GROUP BY node.Id, node.Title, node.LeftIndex, node.RightIndex, node.MetaTitle, node.MetaDescription, node.MetaKeywords, node.Cover
                    ORDER BY node.LeftIndex

get_top_levels_category_by_depth.sql

SELECT col.Id, col.Title, col.LeftIndex, col.RightIndex, col.MetaTitle, col.MetaKeywords, col.MetaDescription, col.Cover, col.depth FROM
(
SELECT node.Id, node.Title, node.LeftIndex, node.RightIndex, node.MetaTitle, node.MetaKeywords, node.MetaDescription, node.Cover, (COUNT(parent.Title) - 1) AS depth 
FROM {0}.Categories AS node, {0}.Categories AS parent 
WHERE (node.LeftIndex BETWEEN parent.LeftIndex AND parent.RightIndex)
GROUP BY node.Id, node.Title, node.LeftIndex, node.RightIndex, node.MetaTitle, node.MetaKeywords, node.MetaDescription, node.Cover
) AS col 
WHERE col.depth = {1}

nested_tree_breadcrumb.sql

SELECT parent.Id, parent.Title, parent.LeftIndex, parent.RightIndex, node.MetaTitle, node.MetaKeywords, node.MetaDescription, node.Cover
FROM {0}.Categories AS node, {0}.Categories AS parent
WHERE node.LeftIndex BETWEEN parent.LeftIndex AND parent.RightIndex
AND node.Id = {1} 
ORDER BY parent.Id, parent.Title, parent.LeftIndex, parent.RightIndex  DESC

update_nodes.sql

-- drag and drop 
DECLARE @left_sibling_lft SMALLINT; 
DECLARE @left_sibling_rgt SMALLINT; 
DECLARE @right_sibling_lft SMALLINT; 
DECLARE @right_sibling_rgt SMALLINT;

SET @left_sibling_lft = (SELECT LeftIndex FROM Category WHERE Id = {0});
SET @left_sibling_rgt = (SELECT RightIndex FROM Category WHERE Id = {0});

SET @right_sibling_lft = (SELECT LeftIndex FROM Category WHERE Id = {1});
SET @right_sibling_rgt = (SELECT RightIndex FROM Category WHERE Id = {1});

UPDATE Category 
SET LeftIndex = CASE 
		WHEN LeftIndex BETWEEN @left_sibling_lft AND @left_sibling_rgt THEN @right_sibling_rgt + LeftIndex - @left_sibling_rgt
		WHEN LeftIndex BETWEEN @right_sibling_lft AND @right_sibling_rgt THEN @left_sibling_lft + LeftIndex - @right_sibling_lft
		ELSE @left_sibling_lft + @right_sibling_rgt + LeftIndex - @left_sibling_rgt - @right_sibling_lft END,
	RightIndex = CASE
		WHEN RightIndex BETWEEN @left_sibling_lft AND @left_sibling_rgt THEN @right_sibling_rgt + RightIndex - @left_sibling_rgt
		WHEN RightIndex BETWEEN @right_sibling_lft AND @right_sibling_rgt THEN @left_sibling_lft + RightIndex - @right_sibling_lft
		ELSE @left_sibling_lft + @right_sibling_rgt + RightIndex - @left_sibling_rgt - @right_sibling_lft END
		
WHERE LeftIndex BETWEEN @left_sibling_lft AND @right_sibling_rgt AND 
@left_sibling_lft < @left_sibling_rgt AND 
@left_sibling_rgt < @right_sibling_lft AND 
@right_sibling_lft < @right_sibling_rgt