Recurring Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Recurring Query

Author

Topic

Ascii255
Starting Member

2 Posts

Posted - 2006-01-24 : 16:32:15

Here's my situation:

I have an itenary table with 2 columns that holds relations between users. A sample dataset would be:
UserID FriendID
1 2
1 4
1 3
2 1
2 4
3 4
etc

I am trying to construct a query where I can check whether a person is within 3 hops from a given userID. I managed to do this with a (kind of lame) set of union queries you can find below

Select userID from tblUser where userID=mFriendID and userID in (Select friendID from tblFriends where userID=mUserID)
union all Select userID from tblUser where userID=mFriendID and userID in (Select friendID from tblFriends where userID in (Select friendID from tblFriends where userID=mUserID))
UNION ALL Select userID from tblUser where userID=mFriendID and userID in (Select friendID from tblFriends where userID in (Select friendID from tblFriends where userID in (Select friendID from tblFriends where userID=mUserID)));

I have several problems with this query. First of all, it checks for a fixed depth of 3. I can not specify the desired depth. What's more it takes a base query Select friendID from tblFriends where userID=mUserID and repets this query as subqueries in other queries - so I basically repeat the same query which will cause trouble considering that I'm planning to hold 20 records per user (on the avg) for a couple of hundred thousands of user.

I use the same lame approach to calculate the number of friends a user can reach through other users within 3 steps:
SELECT count(UserID)
FROM [Select userID from tblUser where not userID=mUserID and userID in (Select friendID from tblFriends where userID=mUserID)
union all Select userID from tblUser where not userID=mUserID and userID in (Select friendID from tblFriends where userID in (Select friendID from tblFriends where userID=mUserID))
UNION Select userID from tblUser where not userID=mUserID and userID in (Select friendID from tblFriends where userID in (Select friendID from tblFriends where userID in (Select friendID from tblFriends where userID=mUserID)))]. AS [%$##@_Alias];

I thought of using temporary tables how ever I'm not sure about its implications on performance (I know it's probably better than this query :) )

So I need your help to create these stored procedure.

Thanks a lot

Norwich
Posting Yak Master

158 Posts

Posted - 2006-01-25 : 04:54:08

Your example is not clear but a good way to handle n Level queries is listed in the link below

[url]http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx [/url]

If you want your computer to be faster then throw it out of the window.

Ascii255
Starting Member

2 Posts

Posted - 2006-01-25 : 15:15:59

I'll try to clarify:

I'm doing an alumni web site. Where people can contact other graduates through their fellow clasmates.

The table is a fairly basic (2 columns) table which holds relationships between users through their userIDs.

userID friendID
1 2
2 1
2 3
3 4
3 5
4 5
4 3
5 6
5 3
5 4

According to the sample dataset users 2 and 3 are friends of user 1.

I am trying to figure out given a userID, how many steps it takes to reach a particular person. e.g. user 1 reaches user 6 through route 1-2-3-5-6 in 4 steps. So I need a query where I can determine how many steps it takes a particular user to reach another given user (e.g.how many steps does it take to reach userID=6 from userID=1).

Then tweaking this query I'll construct a query which determines whether a friend can be reached within a given number of steps from a particular user (e.g.is userID 6 within 3 steps from userID=1).

I'll also use the same basis to build a query where I can find the number of users that exist within a given number of steps (e.g.how many friends are within 3 steps from userID=1)

As I said, I managed to build these queries with lame union operations. But I think it'd be better to use temp tables or cursors.

Hope I could clear things

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources