Handling hierachical structures in SQL
Dealing with tree structures in SQL can be tricky. A common solution is to have a self referencing table with a primary key and parent key column. This allows for any depth of tree and allows each 'node' to have zero or more child nodes.
Below is a stored proc that is a good base for selecting a sub-tree from any sort of single table hierachy.
The example comes from a tree of 'targets'. Hence the table contains TargetID as athe primary key and ParentTargetId as the parent id.
CREATE PROCEDURE [dbo].[GetSortedSubTree]( @TargetID bigint )
AS
DECLARE @IDS TABLE ( id int PRIMARY KEY, Depth int, NodePath varchar(1000) )
DECLARE @Depth int
SET @Depth=1
DECLARE @Done bit
SET @Done=0
-- insert top of tree
INSERT @IDS( id, Depth, NodePath )
SELECT TargetID, @Depth, CONVERT( VARCHAR(1000), TargetID )
FROM tblTargets WHERE TargetID = @TargetID or
(ParentTargetId is null and @TargetId =0)
IF @@rowcount=0
SET @Done=1
WHILE @Done=0
BEGIN
SET @Depth=@Depth+1
INSERT @IDS( id, Depth, NodePath )
SELECT TargetID, @Depth, ids.NodePath+'|'+CONVERT( VARCHAR(1000), TargetID )
FROM tblTargets t
JOIN @IDS ids ON ids.id = t.ParentTargetID
WHERE ids.Depth=(@Depth-1)
IF @@rowcount=0
SET @Done=1
END
SELECT t.TargetID, t.TargetText, ids.Depth, ids.NodePath
FROM tblTargets t
JOIN @IDS ids ON ids.id = t.TargetID
ORDER BY ids.NodePath
Note that the node path is the limiting factor as regards to depth -= but even using a varchar(1000) allows for a considerable depth.