Computing Minimum Path Lengths In An Sql Table

I'm having a problem with this exercise: Table Friend: Friend1 Friend2 Table Relationship: Friend1 Friend2 GradeOfFriendship I need to create a trigger in which I

Solution 1:

This is one way to create recursive fried network:

;with DATA as (
  select Person1, Person2, 1as Grade from Friends
  unionselect Person2, Person1, 1as Grade from Friends
),
CTE as (
  select Person1, Person2, Grade,
    convert(varchar(max), '|'+ Person1 +'|'+ Person2 +'|') as Path
    from DATA
  unionallselect C.Person1, D.Person2, C.Grade+1, C.Path+D.Person2+'|'from CTE C join DATA D on C.Person2 = D.Person1
  where C.Person1 <> D.Person2 and C.Path notlike'|%'+D.Person2 +'%|'
)

select Person1, Person2, min(Grade)
from CTE
groupby Person1, Person2
orderby1,3,2

The first CTE called DATA is there so that there's no need to have friedships entered both ways into friend table. If you have them that way already, you can leave it out.

The second CTE called CTE is recursive, and it fetches all the paths from one person to another. The path -column with names separated by | is there to prevent endless loops when friendships make circles.

The final select picks only the shortest path between friends.

Example in SQL Fiddle

Solution 2:

This is an outer join between friends and grades