help with query - friends of friends relationship

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 2005 Forums

Transact-SQL (2005)

help with query - friends of friends relationship

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-03 : 04:19:27

Hi,

I'm looking to write a "friends of friends" query and I'm not exactly sure what the best way to do it is.

I have a table as such and want to be able to select level 2 friends basically. I want this list to be a unique list of the userID's, and if its possible have a count(*) as well.

Thanks very much for any assistance! :)

mike123

For example

userID/friendID

1 / 500
1 / 400
1 / 300

500 / 22
500 / 33
400 / 22
300 / 11

The results returned would be

friendID
22
33
11

OR

friendID / count
22 /2
33 /1
11 /1

CREATE TABLE [dbo].[tblFriends](
[UserID] [int] NOT NULL,
[FriendID] [int] NOT NULL)

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-03 : 04:48:48

declare @tt table (uid int , fid int)
insert @tt
select 1, 500 union all
select 1, 400 union all
select 1, 300 union all
select 500, 22 union all
select 500, 33 union all
select 400, 22 union all
select 300, 11

select fid, Count = Count(uid) from
(select * from @tt where uid in (select fid from @tt where uid =1)) a group by fid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-03 : 05:29:16

[code]-- prepare sample data
declare @tt table (uid int, fid int)

insert @tt
select 1, 500 union all
select 1, 400 union all
select 1, 300 union all
select 500, 22 union all
select 500, 33 union all
select 400, 22 union all
select 300, 11

-- show the expected output
select t2.fid as friendid,
count(*) as count
from @tt as t1
inner join @tt as t2 on t2.uid = t1.fid
where t1.uid = 1
group by t2.fid
order by 2 desc,
1[/code]

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 05:31:24

for any kind of recursion use a new feature in Sql Server 2005 called CTE = Common table expressions.
look it up in BOL.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources