SQL Server - How to manage hierarchical data in a table?
I am using SQL Server 2000.
Suppose I have two tables:
Area
----------------------------------
ID| Name | HierarchyLevel
----------------------------------
1 | World | 1
2 | America| 2
3 | Europe | 2
4 | Africa | 2
5 | USA | 3
and
AreaHierarchy
------------------------
ID | ParentID | ChildID
------------------------
1 | 1 | 2
2 | 1 | 3
3 | 1 | 4
4 | 2 | 5
Where
AreaHierarchy.ParentID and AreaHierarchy.ChildID are FKs of Area.ID
How can I find the nth US parent?
Is this possible without loops?
Probably no.
source to share
No loops, no recursion
It is best to add an extra field to the second table, which will be named ie. Parents
and will just keep the parent ids in a string like:
AreaHierarchy
------------------------------------
ID | ParentID | ChildID | Parents
------------------------------------
1 | 1 | 2 | 1/
2 | 1 | 3 | 1/
3 | 1 | 4 | 1/
4 | 2 | 5 | 1/2/
This way you can easily reach any parent in the branch without recursion or any other complicated procedure. The processing cost is very small, you just copy the parent value Parents
and add another identifier. And since you probably need to read more than write / update, this is the best solution to your problem.
And if I were you, I would just store one table per your data. Join both tables to one. The level can also be calculated based on the slash count in the Parents
varchar value , but I would not recommend doing that.
Additional "catch" you should know
If your data is mostly read / write and there are a lot fewer updates, this structure is really efficient. But if your table is doing a lot more updates than reading / writing, you should avoid this technique. What for? Imagine you have a very deep tree with many children. Changing the parent element at some node high in the root directory means that you have to update Parents
all nodes in the subtree.
source to share
Must work
CREATE PROCEDURE find_nth_parent
@id INT,
@level INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @counter INT
SET @counter = 1
DECLARE @currentItem INT
DECLARE @currentItemNew INT
SET @currentItem = @id
WHILE @counter <= @level
BEGIN
SET @currentItemNew = NULL
SELECT @currentItemNew = ParentID FROM AreaHierarchy WHERE ChildId = @currentItem
IF @currentItemNew IS NULL
BEGIN
SELECT NULL
RETURN
END
SET @currentItem = @currentItemNew
SET @counter = @counter + 1
END
SELECT @currentItem
END
Call
EXEC find_nth_parent 5,2
returns 1 which means "World" (2nd parent) by calling
EXEC find_nth_parent 5,1
return 2 which means "America" (1st parent).
Hope it helps
source to share
You can use recursion. If you have SQL Server 2005 or newer, you can use Common Table Expressions. If not, you really need to use custom functions.
An example UDF for this would be ...
CREATE FUNCTION get_nth_parent(area_id AS INT, n as INT)
RETURNS INT
AS
IF (n = 0) RETURN area_id
DECLARE @return INT
SELECT
@return = dbo.get_nth_parent(AreaHierarchy.ParentID, n-1)
FROM
AreaHierarchy
WHERE
ChildID = area_id
RETURN @return
An example of using Common Table Experessions could be ...
DECLARE @hierarchy TABLE (
parent_id INT,
child_id INT
)
INSERT INTO @hierarchy SELECT 1,2
INSERT INTO @hierarchy SELECT 1,3
INSERT INTO @hierarchy SELECT 1,4
INSERT INTO @hierarchy SELECT 2,5
;WITH
relative_distance (
child_id,
parent_id,
distance
)
AS
(
SELECT
child_id,
parent_id,
1
FROM
@hierarchy
UNION ALL
SELECT
[relative_distance].child_id,
[hierarchy].parent_id,
[relative_distance].distance + 1
FROM
[relative_distance]
INNER JOIN
@hierarchy AS [hierarchy]
ON [hierarchy].child_id = [relative_distance].parent_id
)
SELECT
parent_id
FROM
[relative_distance]
WHERE
child_id = 5
AND distance = 2
source to share
In SQL Server 2005+, you use CTE in a function:
create function get_parent(@child as int, @parent_level as int)
returns int
as
begin
declare @parent int
;with parentage as (
select
h.parent_id,
h.child_id,
0 as level
from
areahierarchy h
where
h.child_id = @child
union all
select
h.parent_id,
h.child_id,
p.level + 1 as level
from
areahierarchy h
inner join parentage p on
h.parent_id = p.child_id
where
p.level < @parent_level
)
select @parent = p.child_id from parentage p
where level = (select max(level) from parentage)
return @parent
end
source to share
You can use Joe Celko's nested set model https://en.wikipedia.org/wiki/Nested_set_model
or even better Closure Table Model
source to share