May be this soluton seems to be funny to your experienced people. But my views on this are.
this friends mechanism resembles me a graph datastructure.
if we apply the breadth first search algorithm to that procedure, and then after finding the required node, if we apply recursive traversal. i think we can get solution.
actually the below solution is i tried it for some other post previously.
I created some tables and tried.
create table persons
(
PersonId int
PersonName varchar(100)
)
create table Friends
(
FriendRelationId int identity(1,1),
FSource int,
FDestination int
)
create proc sc_test
(
@FSourceId int,
@FDestinationId int,
@MaxLevel int
)
as
begin
declare @temp table
(
TableId int identity(1,1),
FSourceId int,
myLevel int,
ParentId int
)
declare @Result table
(
ResultId int identity(1,1),
FSourceId int,
FDestinationId int
)
declare @TableId int
declare @CurId int
declare @CurLevel int
declare @ParentId int
set @TableId = 1
insert into @temp select FDestination,1,@FSourceId from Friends where FSource = @FSourceId
while((select count(*) from @temp)>0)
begin
set @CurId = (select top 1 FSourceId from @temp where TableId = @TableId)
if(@CurId = @FDestinationId)
begin
goto l1
end
else
begin
set @CurLevel = (select top 1 myLevel from @temp where FSourceId = @CurId)
if(@CurLevel > @MaxLevel)
begin
goto l2
end
insert into @temp select FDestination,@CurLevel + 1,@CurId from Friends where FSource = @CurId and FDestination not in (select FSourceId from @temp)
end
set @TableId = @TableId + 1
end
l1:
set @CurLevel = (select myLevel from @temp where TableId = @TableId)
insert into @Result select ParentId,FSourceId from @temp where TableId = @TableId
set @TableId = (select ParentId from @temp where TableId = @TableId)
set @CurLevel = @CurLevel - 1
while(@CurLevel > 0)
begin
insert into @Result (FSourceId,FDestinationID) select ParentId,FSourceId from @temp where FSourceId = @TableId
set @TableId = (select ParentId from @temp where FSourceId = @TableId)
set @CurLevel = @CurLevel - 1
end
select FSourceId,FDestinationId from @Result order by ResultId desc
l2:
end