Nested/Hierarchical table query
-
I have used Joe Celko's model of Nested model. Now I want the following query. I want a tree (records) where ParentID and NoOfTreeNodes wanted are parameterized. Example:
ID Name ParentID
1 Cameras null
2 Digital 1
3 Ordinary 1
4. Sony 2
5. HP 2
6. Sony 3
7. HP 3
8 Optical 4
9 Ordinary 4
10 1 mp 8
11 2 mp 8
GetTree(2, 2) should give me records from 4,8,9,5 in that order.
-
Hmm... this thing lost my post.
Anyway - here's a solution. It only works for smallish trees, because of the way the ordering is done.
/* --This is just to populate my sample data.
select * into dbo.tree from
(select 1 id, 'Cameras' name,null parentid
union all select 2, 'Digital' name, 1 parentid
union all select 3, 'Ordinary', 1
union all select 4, 'Sony', 2
union all select 5, 'HP', 2
union all select 6, 'Sony', 3
union all select 7, 'HP', 3
union all select 8, 'Optical', 4
union all select 9, 'Ordinary', 4
union all select 10, '1 mp', 8
union all select 11, '2 mp', 8) t
*/
--You would put this into a stored proc or udf, and use parameters for these.
declare @parent int
set @parent = 2
declare @depth int
set @depth = 2
declare @length int
select @length = len(cast(max(id) as varchar(100)))
from dbo.tree
declare @Tmp table (id int, orderby varchar(7000), processed tinyint)
insert into @Tmp
select
id,
right(space(@length) + cast(id as varchar(100)),@length) orderby,
0 processed
from dbo.tree
where parentid = @parent
declare @depthsofar int
set @depthsofar = 1
while (@depthsofar < @depth)
begin
update @Tmp set processed = processed + 1 where processed < 2
insert into @Tmp
select tree.id,
tmp.orderby + right(space(@length) + cast(tree.id as varchar(100)),@length),
0
from @Tmp tmp
join
dbo.tree tree
on tree.parentid = tmp.id
where tmp.processed = 1
if (@@rowcount = 0) set @depthsofar = @depth
set @depthsofar = @depthsofar + 1
end
select id
from @Tmp
order by orderby
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
Try these functions!
CREATE FUNCTION dbo.fnGetTree-- returns csv
(
@ParentID INT,
@Levels INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
IF @Levels < 1
RETURN ''
DECLARE @Tree TABLE
(
TreeID INT IDENTITY(0, 1),
TreeKey VARBINARY(8000),
Generation SMALLINT,
ParentID INT,
ID INT
)
INSERT @Tree
(
Generation,
ParentID,
ID
)
SELECT @Levels,
TreeTable.ParentID,
TreeTable.ID
FROM TreeTable
WHERE TreeTable.ParentID = @ParentID
ORDER BY TreeTable.ID
UPDATE @Tree
SET TreeKey = CAST(TreeID AS BINARY(4))
WHILE @Levels > 1
BEGIN
SELECT @Levels = @Levels - 1
INSERT @Tree
(
TreeKey,
Generation,
ParentID,
ID
)
SELECT t.TreeKey,
@Levels,
TreeTable.ParentID,
TreeTable.ID
FROM TreeTable
INNER JOIN @Tree t ON t.ID = TreeTable.ParentID
WHERE TreeTable.ParentID IN (SELECT ID FROM @Tree WHERE Generation = @Levels + 1)
ORDER BY TreeTable.ID
UPDATE @Tree
SET TreeKey = TreeKey + CAST(TreeID AS BINARY(4))
WHERE Generation = @Levels
END
DECLARE @NodeText VARCHAR(8000)
SELECT @NodeText = ISNULL(@NodeText + ',', '') + CONVERT(VARCHAR, z.ID)
FROM (
SELECT TOP 100 PERCENT ID
FROM @Tree
ORDER BY TreeKey
) z
RETURN @NodeText
END
OR
CREATE FUNCTION dbo.fnGetTree-- returns resultset
(
@ParentID INT,
@Levels INT
)
RETURNS @Output TABLE (ID INT)
AS
BEGIN
IF @Levels < 1
RETURN
DECLARE @Tree TABLE
(
TreeID INT IDENTITY(0, 1),
TreeKey VARBINARY(8000),
Generation SMALLINT,
ParentID INT,
ID INT
)
INSERT @Tree
(
Generation,
ParentID,
ID
)
SELECT @Levels,
TreeTable.ParentID,
TreeTable.ID
FROM TreeTable
WHERE TreeTable.ParentID = @ParentID
ORDER BY TreeTable.ID
UPDATE @Tree
SET TreeKey = CAST(TreeID AS BINARY(4))
WHILE @Levels > 1
BEGIN
SELECT @Levels = @Levels - 1
INSERT @Tree
(
TreeKey,
Generation,
ParentID,
ID
)
SELECT t.TreeKey,
@Levels,
TreeTable.ParentID,
TreeTable.ID
FROM TreeTable
INNER JOIN @Tree t ON t.ID = TreeTable.ParentID
WHERE TreeTable.ParentID IN (SELECT ID FROM @Tree WHERE Generation = @Levels + 1)
ORDER BY TreeTable.ID
UPDATE @Tree
SET TreeKey = TreeKey + CAST(TreeID AS BINARY(4))
WHERE Generation = @Levels
END
INSERT @Output
SELECT ID
FROM @Tree
ORDER BY TreeKey
RETURN
END
N 56°04'39.16"
E 12°55'05.25" -
Just to clarify: This is an 'Adjacency List Model', rather than "Joe Celko's 'Nested Set Model'".
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Thanks to Rob Farley and Peter Larsson. I have tested Peter's code and it works beautifully. I have never used VarBinary, so have to dig deep into this.
To Ryan Randall - nice catch. Yes of course this is Adjacency model and not Nested set. But I forgot to write that I have a nested model based on this adjacency model. While using Celko's method to get the tree, it works. But my Adjacency table contains more fields and as soon as I join the table, I lose the tree format. If anybody have answer to this, please post it here.
Thanks to everybody who helped.
-
Thank you.
There is one drawback with my method. You can only have 2,000 levels of nodes. But you can have 2 billion nodes in total!
For the CSV method, the output string can only hold 8,000 characters.
N 56°04'39.16"
E 12°55'05.25"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply