April 17, 2008 at 11:34 pm
Greetings,
I'm trying to come up with a way (efficiency isn't the most important thing here) ..to show how a person is related to another person on my website. The table structure is like so (simplified)
ID | UserName| FriendName
1 | Danny | Mike
2 | Mike | Danny
3 | Steve | Jason
4 | Jason | Steve
5 | Steve Danny
6 | Danny | Steve
There are 2 rows each for every 'relationship' - I chose this design to simplify queries like "give me all of danny's friends" etc.
With this stucture, I want to be able to display to a user how they are "related" to someone that they may not necessarily be friends with. For example, lets say you are logged in as "Jason" and you are looking at "Dannys" homepage.
You are not Danny's direct friend..but you are related to him through Steve (because you are a friend of steve, and steve is a friend of jason)
I'm stumped trying to come up with a decent way to get this type of information..either in a sproc...or doing it in the business layer (c#). I think recursion is the way to go, and I could limit it to 4,5,6 levels whatever... again, efficiency isn't an issue here...I just want to somehow pull the data cleanly.
Any ideas?
April 18, 2008 at 1:09 am
I have answered hundreds of these type of questions over at SQLTeam.com
For one example see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72097
You can easily edit the fnCommonFriendsStep function to include member names along the way.
Then you can use the algorithm to rewrite the code to a recursive CTE.
N 56°04'39.16"
E 12°55'05.25"
April 18, 2008 at 8:16 am
Exactly what I was looking for, thanks!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply