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 must obtain symmetric tuple, for example:
Luc Mark
Mark Luc
in both tables.
If there is a direct contact between two people then their GradeOfFriendship = 1
If there is no contact between a pair of people then GradeOfFriendship = 0
.
In other cases the GradeOfFriendship
must be calculated as the minimum distance over all possible paths connecting these two people (we must consider this table as a directed graph)
My problem is not to obtain a symmetric tuple, but how to calculate all the possible paths between two people. For example:
Luc Marc 1
Marc John 1
Luc John 2
I am using SQL Server. At the moment I don't have any idea how solve this problem - I think that I must use some recursive function but I don't know how....
This is an outer join between friends and grades