General table expressions, how to avoid infinite recursion when moving a graph?

Question

I have a simple weighted graph

    A
 1 / \\ 0.5
  /   \\0.5
 B     C

Suppose this describes a family, with A a father, B a son, and C a mother. Let them say that B is studying at the university, and A bought an apartment for him. A lives with C in a house that usually belongs to 50-50.

I want to convert a graph to a tree starting with A: ie.

• A owns 50% of the place C living in
• A owns 100% of the place B lives in
• C owns 50% of the place A living in

The graph and the generated tree may be more complex, but I hope you get a bigger picture.

In SQL Server 2005, I have

Drop Table #graph;
Create Table #graph
    (FirstVertex VarChar(1) Not Null, 
     SecondVertex VarChar(1) Not Null, 
     Weight float);

Insert #graph Values('A','B',1);
Insert #graph Values('A','C',0.5);
Insert #graph Values('C','A',0.5);

and I use the following general table expression to move the graph, starting with 'A':

With GraphRecursion (FirstVertex, SecondVertex, Weight, Level)
As
(
    Select FirstVertex, SecondVertex, Weight, 0 As Level
    From #graph
    Where FirstVertex='A'

    Union all

    Select a.FirstVertex, a.SecondVertex, a.Weight, b.Level+1
    From #graph a 
    Inner Join GraphRecursion b
    On a.FirstVertex=b.SecondVertex --And b.Level<=1
)
Select * From GraphRecursion;

It causes

Msg 530, Level 16, State 1, Line 11
The statement terminated. The maximum recursion 100 has 
been exhausted before statement completion.

And b.Level<=1 , , , .

, (.. FirstVertex, SecondVertex) ?

Answer 1

, . - ( , ):

With GraphRecursion (FirstVertex, SecondVertex, Weight, Level,Nodes)
As
(
    Select FirstVertex, SecondVertex, Weight, 0 As Level,CONVERT(varchar(8000),':' + FirstVertex + ':' + SecondVertex + ':')
    From #graph
    Where FirstVertex='A'

    Union all

    Select a.FirstVertex, a.SecondVertex, a.Weight, b.Level+1,b.Nodes + ':' + a.SecondVertex  + ':'
    From #graph a 
    Inner Join GraphRecursion b
    On a.FirstVertex=b.SecondVertex --And b.Level<=1
    where not b.Nodes like '%:' + a.SecondVertex + ':%'
)
Select * From GraphRecursion;

, , , . ':' + FirstVertex + '@' + SecondVertex + ':'. ':' '@' , . ( - b.Level <= 1, ):

With GraphRecursion (FirstVertex, SecondVertex, Weight, Level,Nodes)
As
(
    Select FirstVertex, SecondVertex, Weight, 0 As Level,CONVERT(varchar(8000),':' + FirstVertex + '@' + SecondVertex + ':')
    From #graph
    Where FirstVertex='A'

    Union all

    Select a.FirstVertex, a.SecondVertex, a.Weight, b.Level+1,b.Nodes + ':' + a.FirstVertex + '@' + a.SecondVertex  + ':'
    From #graph a 
    Inner Join GraphRecursion b
    On a.FirstVertex=b.SecondVertex --And b.Level<=1
    where not b.Nodes like '%:' + a.FirstVertex + '@' + a.SecondVertex + ':%'
)

( , b.Level <= 1 5 , ). , . b.Level <= 1 2 a- > c, c- > a, a- > c, ,