自我加入多对多的关系

From sample data below, assuming Julie (1) has friends Adam, David, John (2, 3, 4). Adam (2) has friends Julie, David, John (1, 3, 4).

根据以下样本数据，假设Julie（1）有朋友Adam，David，John（2,3,4）。亚当（2）有朋友朱莉，大卫，约翰（1,3,4）。

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

This make a self join and many-to-many relationship within ONE table.

这使得一个表中的自连接和多对多关系成为可能。

In addition to the above problem, say Julie (1) added Sam (5) as friends, technically and practically speaking, Sam (5) is now friend of Julie (1) as well. This make things more complicated because the relationship bi-directional.

除了上述问题之外，Julie（1）还说Sam（5）是朋友，从技术上和实际上讲，Sam（5）现在也是Julie（1）的朋友。这使得事情变得更加复杂，因为这种关系是双向的。

So I'm wondering:

所以我想知道：

How do I design the database?
我该如何设计数据库？
How do I make a query that will return all friends of every users?
如何进行将返回每个用户的所有朋友的查询？

Thank you!

谢谢！

4 个解决方案

#1

Example 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

SQL Fiddle演示：http：//sqlfiddle.com/#！2/97b41/6/0

This will work regardless of whether or not you record both directions on the friendships table.

无论您是否在友谊表上记录两个方向，这都将有效。

#2

One approach could be that you create second table that stores the person and friend ids. In this scenario, consider the following tables.

一种方法可能是您创建存储人员和朋友ID的第二个表。在此方案中，请考虑以下表格。

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);

Now the Friend table will store the user_id and his/her friend_id. For getting the list of friends for a particular user, you can search the id matching in either of these two columns. Below are sample queries.

现在，Friend表将存储user_id和他/她的friend_id。要获取特定用户的朋友列表，您可以在这两列中的任意一列中搜索id匹配。以下是示例查询。

-- 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 idea with this and can play around.

我希望你对此有所了解并且可以玩。

#3

Pretty much agree with the others. You need a link table. I'll give a bit more detail.. some examples of keys and indexes and the query you wanted (bi-directional).

非常赞同其他人。你需要一个链接表。我将给出更多详细信息..键和索引的一些示例以及您想要的查询（双向）。

    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

#4

I tried whatever you written in your query:

我试过你在查询中写的任何内容：

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

#1