December 15, 2006 at 7:36 am
hi
i have a hierarchical structure, a graph
i need to walk on it ( scan it )
ensuring that i pass just one time
over the same node ( row )
using a CTE for this graph
___ C_________ D _
/ \ \
A \ F
\_____ B ______ E __/
i get ( from left to right precedence order )
A, B
B, E *
E, F *
A, C
C, B
B, E --> repeated *
E, F --> repeated *
C, D
D, F
i tried with an additional "left join" inside the CTE
in order to filter scanned nodes ,
but the command supports just ONE join to the recursive expression
i know that i can use a DISTINCT outside the command
but what i'm trying to do, is avoiding
to scan scanned paths again and again
how can i make it work ?!
thanks !
--
atte,
Hernán
December 18, 2006 at 5:10 am
I have done this for a customer requiring all routes through a network. The outer query then decides the rows to show. You can have two possible start (stub) queries - 1. a node with no preceding nodes and 2. all nodes.
create table bases(
basename varchar(10)
)
create table baselink(
frombase varchar(10),
tobase varchar(10),
distance int
)
insert into bases values ('A')
insert into bases values ('B')
insert into bases values ('C')
insert into bases values ('D')
insert into bases values ('E')
insert into bases values ('F')
insert into baselink values ('A','B',1)
insert into baselink values ('A','C',2)
insert into baselink values ('C','B',2)
insert into baselink values ('C','D',3)
insert into baselink values ('B','E',4)
insert into baselink values ('D','F',1)
insert into baselink values ('E','F',2)
insert into baselink values ('E','D',1)
select * from bases;
select * from baselink;
-- From start point with no precedents ie A
with Routes as
(
select basename as 'Starts',basename as 'ToBase', convert(varchar(20),basename) as 'Links',0 as 'Dist'
from bases left join baselink
on bases.basename = baselink.tobase
where frombase IS NULL
union all
select starts,baselink.tobase,convert(varchar(20),Links+','+baselink.tobase),Dist+Distance
from Routes inner join baselink
on Routes.tobase = baselink.frombase
)
select starts,tobase,Dist,Links
from Routes;
-- From all start points
with Routes as
(
select basename as 'Starts',basename as 'ToBase', convert(varchar(20),basename) as 'Links',0 as 'Dist'
from bases
union all
select starts,baselink.tobase,convert(varchar(20),Links+','+baselink.tobase),Dist+Distance
from Routes inner join baselink
on Routes.tobase = baselink.frombase
)
select starts,tobase,Dist,Links
from Routes;
Hope this helps
Fitz
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply