Self join many-to-many relationship

Question

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

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.

So I'm wondering:

1. How do I design the database?
2. How do I make a query that will return all friends of every users?

Thank you!

Answer 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

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

Answer 2

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