how can write mutual friends query

CREATE TABLE dbo.Person
(ID INT PRIMARY KEY,
Name VARCHAR(25))

CREATE TABLE dbo.Friendship
(PersonID INT,
FriendID INT)

INSERT INTO dbo.Person VALUES ( 1, 'Person01')
INSERT INTO dbo.Person VALUES ( 2, 'Person02')
INSERT INTO dbo.Person VALUES ( 3, 'Person03')
INSERT INTO dbo.Person VALUES ( 4, 'Person04')

-- The Friendship table stores the friendship between two persons
-- in both directions. So the friendship between Person01 and
-- and Person02 is stored as (1, 2) and (2, 1). When inserting or
-- deleting friendships both record need inserting or removing.
INSERT INTO dbo.Friendship VALUES (1, 2)
INSERT INTO dbo.Friendship VALUES (1, 4)
INSERT INTO dbo.Friendship VALUES (2, 1)
INSERT INTO dbo.Friendship VALUES (2, 3)
INSERT INTO dbo.Friendship VALUES (2, 4)
INSERT INTO dbo.Friendship VALUES (3, 2)
INSERT INTO dbo.Friendship VALUES (3, 4)
INSERT INTO dbo.Friendship VALUES (4, 1)
INSERT INTO dbo.Friendship VALUES (4, 2)
INSERT INTO dbo.Friendship VALUES (4, 3)

SELECT P1.Name
FROM dbo.Friendship AS F1
JOIN dbo.Person AS P1 ON P1.ID = F1.FriendID
WHERE F1.PersonID = 1 AND
F1.FriendID IN (SELECT F2.FriendID
FROM dbo.Friendship AS F2
WHERE F2.PersonID = 2)


SELECT P1.Name, P2.Name, P3.Name AS MutualFriend
FROM dbo.Friendship AS F1
JOIN dbo.Friendship AS F2 ON F2.PersonID <> F1.PersonID AND F2.FriendID = F1.FriendID
JOIN dbo.Person AS P1 ON P1.ID = F1.PersonID
JOIN dbo.Person AS P2 ON P2.ID = F2.PersonID
JOIN dbo.Person AS P3 ON P3.ID = F2.FriendID
ORDER BY F1.PersonID, F2.PersonID

create table friends
(
id int identity(1,1),
[user] int,
friend int
)

create table users
(
id int identity(1,1),
[name] varchar(50)
)

go
insert into users default values
go 40
UPDATE users set [name] = 'User' + cast(id as varchar)

go
insert into friends
select id, ABS(CHECKSUM(NewId())) % 40
from users
go 10
delete from friends where [user]=friend
delete from friends where friend = 0
;with cte as
(
select [user],friend, count(*) AS C
from friends
group by [user],friend
HAVING count(*) > 1
)
select max(id) as del INTO #dels
from friends F
INNER JOIN cte ON cte.[user] = F.[user] AND cte.friend=F.friend
GROUP BY F.[user],F.friend

DELETE from friends WHERE id in (SELECT del from #dels)
drop table #dels
GO 10