describing tree paths
-
Hello all,
having trees implemented this way
create table labels(
id bigint not null identity(1,1),
label nvarchar(250) not null,
primary key (id)
)
go
create table nodes
(
ancestor bigint not null,
descendant bigint not null,
length int not null,
primary key (ancestor, descendant),
foreign key (ancestor) references labels (id),
foreign key (descendant) references labels (id)
)
go
insert into labels
select 'A' union all
select 'B' union all
select 'C' union all
select 'D'
go
insert into nodes
select 1,1,0 union all --root
select 1,2,1 union all -- A -> B
select 2,2,0 union all -- B -> B
select 1,3,1 union all -- A -> C
select 3,3,0 union all -- C -> C
select 1,4,2 union all -- A -> D
select 2,4,1 union all -- B -> D
select 4,4,0 -- D -> D
how could i write a query to show the visual text representation of the tree, like
A -> B
A -> B -> D
A -> C
-
Seems an odd representation of a hierarchy but this gives you the correct results
WITH CTE AS (
SELECT la.label AS ancestor,
ld.label AS descendant
FROM nodes n
INNER JOIN labels la ON la.id = n.ancestor
INNER JOIN labels ld ON ld.id = n.descendant
WHERE n.length = 1),
Recur AS (
SELECT c1.ancestor, CAST(c1.ancestor AS VARCHAR(MAX)) AS Path, 1 AS Level
FROM CTE c1
WHERE NOT EXISTS(SELECT * FROM CTE c2 WHERE c2.descendant = c1.ancestor)
GROUP BY c1.ancestor
UNION ALL
SELECT c.descendant, r.Path + ' -> ' + CAST(c.descendant AS VARCHAR(MAX)), r.Level+1
FROM CTE c
INNER JOIN Recur r ON r.ancestor = c.ancestor)
SELECT Path
FROM Recur
WHERE Level > 1
ORDER BY Path;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
why you consider it odd ? i was reading a text on anti - pattern representations, that's from where i took the "idea" of this representation.
-
a20213 (2/5/2015)
why you consider it odd ? i was reading a text on anti - pattern representations, that's from where i took the "idea" of this representation.Because there's a lot of redundancy in the nodes table - everything with length != 1 isn't used.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
I think the length column even be removed, still don't know exactly why why it helps being there.
If i edit the part
WHERE n.length = 1)forWHERE la.id <> ld.idi get the extra path A -> DI marked the answer as correctly ) .
Thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply