SQL Server: search for 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 common words. Names are in two columns in one table. Only “foster” friends should be returned.

Here is an example of 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 other between the two (and the desired outcome) is Joe. Sally is common to both, but Jimmy has not yet accepted her as a friend.

Answer 1

A slightly less elegant, but perhaps more effective solution might be:

 DECLARE @person1 VARCHAR(50) = 'Jimmy' DECLARE @person2 VARCHAR(50) = 'John' SELECT FriendsOfP1.Name as MutualFriend FROM ( SELECT A.name_2 AS Name FROM friends A WHERE ( A.name_1 = @person1 ) AND Accepted = 1 AND name_2 <> @person2 UNION ALL SELECT A.name_1 FROM friends A WHERE ( A.name_2 = @person1 ) AND Accepted = 1 AND name_1 <> @person2 ) AS friendsOfP1 INNER JOIN ( SELECT A.name_2 AS Name FROM friends A WHERE ( A.name_1 = @person2 ) AND Accepted = 1 AND name_2 <> @person1 UNION ALL SELECT A.name_1 FROM friends A WHERE ( A.name_2 = @person2 ) AND Accepted = 1 AND name_1 <> @person1 ) AS FriendsOfP2 ON FriendsOfP1.Name = FriendsOfP2.Name 

Answer 2

Try this double independent connection 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 a mutual friend.

Related SQL Fiddle

Note I’m not sure that this structure is the best for such an attitude, you have the rules of mutual friendship, refusal of friendship, but it looks like you know nothing about this information. Do you know only partial information or is it your storage system / architecture?