sql server - Computing minimum path lengths in an SQL table -

i'm having problem exercise:

table friend:

 friend1     friend2 

table relationship:

 friend1     friend2      gradeoffriendship 

i need create trigger in must obtain symmetric tuple, example:

luc    mark  mark   luc 

in both tables.

if there direct contact between 2 people gradeoffriendship = 1

if there no contact between pair of people gradeoffriendship = 0.

in other cases gradeoffriendship must calculated minimum distance on possible paths connecting these 2 people (we must consider table directed graph)

my problem not obtain symmetric tuple, how calculate possible paths between 2 people. example:

   luc     marc 1    marc    john 1    luc     john 2 

i using sql server. @ moment don't have idea how solve problem - think must use recursive function don't know how....

this 1 way create recursive fried network:

;with data (   select person1, person2, 1 grade friends   union   select person2, person1, 1 grade friends ), cte (   select person1, person2, grade,     convert(varchar(max), '|' + person1 + '|' + person2 +'|') path     data   union   select c.person1, d.person2, c.grade+1, c.path+d.person2+'|'    cte c join data d on c.person2 = d.person1   c.person1 <> d.person2 , c.path not '|%'+d.person2 +'%|' )  select person1, person2, min(grade) cte group person1, person2 order 1,3,2 

the first cte called data there there's no need have friedships entered both ways friend table. if have them way already, can leave out.

the second cte called cte recursive, , fetches paths 1 person another. path -column names separated | there prevent endless loops when friendships make circles.

the final select picks shortest path between friends.

example in sql fiddle