StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You could try something like this</p> <pre><code>set nocount on; -- Your existing table declare @user table (id int, name varchar(25), email varchar(25)) insert into @user select 1,'user a','a@a.com' insert into @user select 2,'user b','b@b.com' insert into @user select 3,'user c','c@c.com' insert into @user select 4,'user d','d@d.com' insert into @user select 5,'user e','e@e.com' -- Your existing table declare @friendships table (id int identity, user_id int, friend_id int) insert into @friendships select 1,2 insert into @friendships select 1,3 insert into @friendships select 1,4 insert into @friendships select 1,5 insert into @friendships select 2,1 insert into @friendships select 2,4 insert into @friendships select 3,1 insert into @friendships select 3,2 insert into @friendships select 3,5 insert into @friendships select 4,1 insert into @friendships select 4,2 insert into @friendships select 4,5 insert into @friendships select 5,1 insert into @friendships select 5,2 insert into @friendships select 5,3 /* Find users with mutual friends */ declare @id int;set @id=4; -- My friends declare @myfriends table (userid int) insert into @myfriends select friend_id from @friendships where user_id=@id --select * from @myfriends -- All other users who have mutual friends with me ;with cteMutualFriends (userid, mutualfriendcount) as ( select user_id,COUNT(*) as mutual from @friendships where friend_id in (select userid from @myfriends) and user_id not in (@id) group by user_id ) select u.*,cteMutualFriends.mutualfriendcount from cteMutualFriends inner join @user u on cteMutualFriends.userid=u.id order by cteMutualFriends.mutualfriendcount desc </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload