SQL Server : find shared items in 2 columns

Question

I am trying to figure out how to write a query for SQL Server that is used to match friends in common. Names are in two columns in the same table. Only "accepted" friends should be returned.

Here's an example of the database information:

ID     Name_1     Name_2     Accepted
=====================================
1      Jimmy      John       1
2      John       Joey       1
3      Joey       Jimmy      1
4      John       Sally      1
5      Jimmy      Sally      0

In this example Jimmy is friends with John. The shared friend between these two (and desired result) is Joey. Sally is common to both as well, but Jimmy has not accepted her as a friend yet.

Answer 1

Try with this double self join for mutual friendship:

SELECT Friend_1, Friend_2, COMMON
FROM
(
SELECT f2.NAME_1 AS Friend_1
      ,f1.NAME_2 AS Friend_2
      ,f2.Name_2 AS COMMON
FROM friends f1
INNER JOIN friends f2
ON f1.NAME_1 = f2.NAME_2
WHERE f1.accepted = 1 AND f2.accepted = 1
) T
INNER JOIN FRIENDS F3
ON (F3.Name_1 = Friend_1 AND F3.Name_2 = Friend_2)
OR (F3.Name_2 = Friend_1 AND F3.Name_1 = Friend_2)
WHERE F3.ACCEPTED <> 0 AND Friend_1 = 'John' AND Friend_2 = 'Jimmy'

COMMON is common friend.

Related SQL Fiddle

Note: I'm not sure this structure is the best one to hold this kind of relation, you have rules on mutual friendship, about friendship rejection but it looks like you'd know nothing about these infos. Do you know only partial informations or is this your storage system/architecture?