August 23, 2016 at 5:39 am
I am trying to build a query. The requirement is to get the number of mutual friends between two users. This I need to do for a single userId passed as a parameter. The result however will be recursive. It will be like a tree. The following table structure will give you an idea about what is the requirement:
DECLARE @Person TABLE
(ID INT PRIMARY KEY,
Name VARCHAR(25))
DECLARE @Friendship TABLE
(PersonID INT,
FriendID INT)
--Person table entry
INSERT @Person
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 3, 'C' UNION ALL
SELECT 4, 'D'
--Person:Friend relationship entry
INSERT @Friendship
SELECT 1, 2 UNION
SELECT 1, 4 UNION
SELECT 2, 1 UNION
SELECT 2, 3 UNION
SELECT 2, 4 UNION
SELECT 3, 2 UNION
SELECT 3, 4 UNION
SELECT 4, 1 UNION
SELECT 4, 2 UNION
SELECT 4, 3
SELECT DISTINCT F1.*, F2.PersonID AS MutualFriend
FROM @Friendship AS F1
INNER JOIN @Friendship AS F2 ON F2.PersonID <> F1.PersonID AND F2.FriendID = F1.FriendID
INNER JOIN @Person AS P1 ON P1.ID = F1.PersonID
INNER JOIN @Person AS P2 ON P2.ID = F2.FriendID
JOIN @Person AS P3 ON P3.ID = F2.FriendID
WHERE F1.PersonID = 3--The person for which friends and mutual friends need to be found
ORDER BY F1.PersonID
The above is a sample query giving the following output:
PersonID FriendID MutualFriend
3 2 1
3 2 4
3 4 1
3 4 2
As you can see, 1 is not a friend of 3 still it is there in the list. The desired output of the query is:
PersonID FriendID MutualFriend
3 2 4
3 4 2
Can someone help me in this query? I need to get the friendId of a userId and the third column as MutualFriendId(If not available then NULL). Count of mutual friends between two users is more appropriate.
August 23, 2016 at 6:57 pm
Two examples:
(1) A is friends with B who is a friend of C. B is a mutual friend.
(2) A is friends with D who is a friend of E who is a friend of F?
Do we care that A has a chain of friendship to F, or are we only concerned with cases like (1) above?
If we're only concerned about (1) above, this should work:
declare @ID int = 3;
;With ListOfFriends as (select PersonID,FriendID from @Friendship where PersonID = @ID)
select LF.personID, LF.FriendID, f2.FriendID as MutualFriend
from ListOfFriends LF
left join @Friendship F2 on LF.FriendID = F2.PersonID
where exists (select 1 from @Friendship f3 where f3.PersonID = f2.FriendID and f3.FriendID = LF.PersonID)
or f2.PersonID is null
order by PersonID,FriendID,MutualFriend
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 25, 2016 at 6:48 am
Hi The Dixie Flatline. Sorry for my late response. Your solution was almost what I needed. There was a little discrepancy in the result. To fix that I had to use:
;
WITH ListOfFriends
AS (
SELECT PersonID
,FriendID
FROM @Friendship
WHERE PersonID = @ID
)
SELECT F1.personID
,F1.FriendID
,F2.FriendID AS [MutualFriend]
FROM ListOfFriends F1
LEFT JOIN @Friendship F2 ON F1.FriendID = F2.personID
WHERE EXISTS (
SELECT 1
FROM ListOfFriends F3
WHERE F3.FriendID = F2.FriendID
AND F1.FriendID = F2.PersonID
)
OR F2.FriendID IS NULL
ORDER BY F1.PersonID
,F1.FriendID
Anyways, thanks a lot for your solution.
August 25, 2016 at 9:53 am
Actually the solution you provided is not giving me the correct result. Not even mine changes are working. For example:
DECLARE @Friendship TABLE
(PersonID INT,
FriendID INT)
--Person:Friend relationship entry
INSERT @Friendship
SELECT 1, 2 UNION
SELECT 1, 4 UNION
SELECT 2, 1 UNION
SELECT 2, 3 UNION
SELECT 2, 4 UNION
--SELECT 3, 2 UNION
SELECT 3, 4 UNION
SELECT 4, 1 UNION
SELECT 4, 2 UNION
SELECT 4, 3
declare @ID int = 3;
;With ListOfFriends as (select PersonID,FriendID from @Friendship where PersonID = @ID)
select LF.personID, LF.FriendID, f2.FriendID as MutualFriend
from ListOfFriends LF
left join @Friendship F2 on LF.FriendID = F2.PersonID
where exists (select 1 from @Friendship f3 where f3.PersonID = f2.FriendID and f3.FriendID = LF.PersonID)
or f2.PersonID is null
order by PersonID,FriendID,MutualFriend
In the above code the result set is:
personIDFriendIDMutualFriend
3 4 2
Infact this is wrong. 2 is not a mutual friend of 3 and 4 as it is not a friend of 3. I hope you will look into this.
August 25, 2016 at 11:53 am
>> The requirement is to get the number of mutual friends between two users. This I need to do for a single user_name passed as a parameter. <<
UNH? Your narrative implies I need two users for the between relationship to work. Why would this be recursive? I think you are using integers for identifiers because you grew up with assembly language programming were integers and pointers were pretty much the same thing. Here is how I would rewrite this problem.
CREATE TABLE Users
(user_name CHAR(2) NOT NULL PRIMARY KEY);
INSERT INTO Users
VALUES ('A'), ('B'), ('C'), ('D');
CREATE TABLE Friendships
(user_name CHAR(2) NOT NULL
REFERENCES Users (user_name)
ON UPDATE CASCADE
ON DELETE CASCADE,
friend_user_name CHAR(2) NOT NULL
REFERENCES Users (user_name)
ON UPDATE CASCADE
ON DELETE CASCADE,
CHECK (user_name <> friend_user_name),
PRIMARY KEY (user_name, friend_user_name));
See how I put a primary key on this? Added constraints, etc. what I do not know from your narrative is whether if X friends Y, then Y is automatically friends with X. The data implies this, but we do not have that as a spec.
INSERT INTO Friendships
VALUES
('A', 'B'), ('B', 'A'),
('A', 'D'), ('D', 'A'),
('B', 'C'), ('C', 'B'),
('B', 'D'), ('D', 'B'),
('C', 'D'), ('D', 'C');
My first guess would be this:
SELECT F1.user_name AS mutual_user_name,
F1.friend_user_name, F2.user_name
FROM Friendships AS F1 LEFT OUTER JOIN Friendships AS F2
ON F1.user_name = F2.friend_user_name;
However, if I understand your original problem, you might try a set oriented approach that will get you the names of the mutual friends, given to users
SELECT X.user_name
FROM
((SELECT F1.friend_user_name
FROM Friendships AS F1
WHERE F1.user_name = @in_first_name)
INTERSECT
(SELECT F2.friend_user_name
FROM Friendships AS F2
WHERE F2.user_name = @in_second_name)
) AS X;
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply