Connect many-to-many relationships

Question

From the data examples below, suggesting that Julia (1) has friends Adam, David, John (2, 3, 4). Adam (2) has friends Julie, David, John (1, 3, 4).

ID Name 1 Julie 2 Adam 3 David 4 John 5 Sam 

This makes self-join relationships and many-to-many relationships within the same table.

In addition to the above problem, let's say Julia (1) added Sam (5) as friends, technically and practically speaking, Sam (5) is now a friend of Julia (1). This complicates the situation because the relationship is bi-directional.

So, I am wondering:

• How do I create a database?
• How to make a request that will return all the friends of each user?

Thanks!

Answer 1

Sample data:

 PEOPLE PERS_ID PERS_NAME 1 Julie 2 Adam 3 David 4 John 5 Sam FRIENDSHIPS PERS_ID FRIEND_ID 1 2 1 3 1 4 2 3 2 4 

Query:

 select people.pers_id as person, people.pers_name as person_name, peoplef.pers_id as friend_id, peoplef.pers_name as friend_name from people join friendships on people.pers_id = friendships.pers_id or people.pers_id = friendships.friend_id join people peoplef on (peoplef.pers_id = friendships.pers_id and peoplef.pers_id <> people.pers_id) or (peoplef.pers_id = friendships.friend_id and peoplef.pers_id <> people.pers_id) order by 2, 4 

SQL Fiddle Demo: http://sqlfiddle.com/#!2/97b41/6/0

This will work regardless of whether you record both directions in the friendship table.

Answer 2

One approach might be that you create a second table that stores the person and friend identifiers. In this case, consider the following tables.

 CREATE TABLE User ( id int auto_increment primary key, name varchar(20) ); CREATE TABLE Friend ( user_id int , friend_id int ); INSERT INTO User (name) VALUES ('Julie'), ('Adam'), ('David'), ('John'), ('Sam'); Insert INTO Friend (user_id, friend_id) values(1, 5), (3, 1); 

The Friend table will now store user_id and his / her friend_id. To get a friend list for a specific user, you can search for matching identifiers in either of these two columns. The following are sample queries.

 -- Get Friends of Julie select 1 AS user_id, IF(user_id = 1, friend_id, user_id) AS friend_id FROM Friend WHERE user_id=1 OR friend_id=1; -- Get Friends of David select 3 AS user_id, IF(user_id = 3, friend_id, user_id) AS friend_id FROM Friend WHERE user_id=3 OR friend_id=3 

I hope you get an idea about this and can play.

Answer 3

Pretty much agree with the rest. You need a table of links. I'll tell you a little more in detail .. some examples of keys and indexes and the request you requested (bidirectional).

  CREATE TABLE dbo.tblUser ( ID int identity(0,1), name varchar(20) CONSTRAINT PK_tblUser PRIMARY KEY (ID) ) -- Many to many link table with FKs CREATE TABLE dbo.tblFriend ( ID1 int not null constraint FK_tblUser_ID1 foreign key references dbo.tblUser(ID), ID2 int not null constraint FK_tblUser_ID2 foreign key references dbo.tblUser(ID) CONSTRAINT PK_tblFriend PRIMARY KEY (ID1, ID2) ) -- Add index (So you can get an index seek if using ID2) CREATE INDEX IX_tblFriend_ID2 ON dbo.tblFriend (ID2) -- Test data INSERT INTO dbo.tblUser(name) VALUES ('Julie'),('Adam'),('David'),('John'),('Sam'); Insert INTO dbo.tblFriend (ID1, ID2) values(0, 1),(2, 0) -- Get bi-directional friend to friend relationships SELECT U1.Name as 'User1', U2.Name as 'User2' FROM dbo.tblFriend F INNER JOIN dbo.tblUser U1 ON U1.ID = F.ID1 INNER JOIN dbo.tblUser U2 ON U2.ID = F.ID2 UNION SELECT U2.Name as 'User1', U1.Name as 'User2' FROM dbo.tblFriend F INNER JOIN dbo.tblUser U1 ON U1.ID = F.ID1 INNER JOIN dbo.tblUser U2 ON U2.ID = F.ID2 ORDER BY User1, User2 

Answer 4

I tried everything that you wrote in your request:

 declare @table table ( id int, name varchar(40) ) insert into @table values (1, 'Julie'), (2, 'Adam'), (3, 'David'), (4, 'John'), (5, 'Sam') select t1.name , t2.name as friend from @table t1, @table t2 where t1.id <> t2.id and t1.id in (1,2) and t2.id <> 5 order by t1.id