SQL query for mutual friends

My MySQL tables structure is like this.

USER
uid

FRIENDS
fuid,fuid2,fapproved

For each friend relationship I insert 2 records in FRIENDS. If user 1 is friend of user 2 then the next rows are 开发者_开发技巧inserted into FRIENDS

1,2,1

2,1,1

1,3,1

3,1,1

2,3,1

3,2,1

User id 3 is friend of user id 1 and user id 2

How to get user id 3 in one sql query?

Given two users @friend1 and @friend2 find all the users who are mutual friends of them:

SELECT user.uid
FROM user
WHERE EXISTS(
    SELECT TOP 1 1 
    FROM Friends 
    WHERE Friends.fuid = @friend1 AND Friends.fapproved = 1 
      AND Friends.fuid2 = User.uid
  )
  AND EXISTS(
    SELECT TOP 1 1 
    FROM Friends 
    WHERE Friends.fuid = @friend2 AND Friends.fapproved = 1 
      AND Friends.fuid2 = User.uid
  )

Try this:

SELECT DISTINCT a.*
    FROM user  a, friends b, friends c
WHERE b.fuid = c.fuid2
    AND b.fuid2 = a.uid
    AND c.fuid = a.uid
    AND b.fapproved = 1
    AND c.fapproved = 1

Test Script(Tried it in MS SQL ..):

CREATE TABLE #USER
(
    uid INT
)

INSERT #USER VALUES(1)
INSERT #USER VALUES(2)
INSERT #USER VALUES(3)


CREATE TABLE #FRIENDS
(
    fuid INT,
    fuid2 INT,
    fapproved  INT
)


INSERT #FRIENDS VALUES(1,2,1)
INSERT #FRIENDS VALUES(2,1,1)
INSERT #FRIENDS VALUES(1,3,1)
INSERT #FRIENDS VALUES(3,1,1)
INSERT #FRIENDS VALUES(2,3,1)
INSERT #FRIENDS VALUES(3,2,1)


SELECT DISTINCT a.*     
    FROM #user  a, #friends b, #friends c 
 WHERE b.fuid = c.fuid2     
     AND b.fuid2 = a.uid     
   AND c.fuid = a.uid     
   AND b.fapproved = 1     
     AND c.fapproved = 1 

$query = "SELECT DISTINCT a.* FROM user  a, visitor b, visitor c WHERE b.user_id = c.visitor_id  AND b.visitor_id = a.user_id AND c.user_id = a.user_id AND b.user_id=65";

i tried it. it is working.

$query = "SELECT f.fuid, f.fuid2 FROM FRIENDS f, USER u WHERE (f.fuid = u.uid or f.fuid2 = u.uid) AND (u.uid = ".$uid.";");

is that what are you searching for?

IMO it's more intuitive to find mutual friends with a self-join on the friendships table

SELECT a.fuid2 FROM facebook_friendships AS b
    ON a.fuid = :user_a AND b.fuid = :user_b AND a.fuid2 = b.fuid2
    AND a.fapproved = 1 AND b.fapproved = 1

Here are the important conditions in English:

• a.fuid = :user_a - user A is friends with user X in friendship A
• b.fuid = :user_b - user B is friends with the user Y in friendship B
• a.fuid2 = b.fuid2 - user A and user B are friends with the same user (i.e., X and Y are the same user)

So you are basically grabbing all the rows that the conditions I listed above. You may get duplicate results so adding DISTINCT around a.fuid2 would be a good idea.

i'm just starting MySQL and i faced this problem.

personID    friendID
6           10
6           2
6           3
8           1
8           2
8           3

/*  query for friends   */
select f.personID, p.personID, firstName, lastName
from person p
inner join friends f on f.friendID = p.personID
where f.personID = 6;

/*  query for common friends    */
select f1.personID 'personID 1', f2.personID 'personID 2', f1.friendID 'common friend'
from person p
inner join friends f1 on f1.friendID = p.personID
inner join friends f2 on f2.friendID = p.personID
where f1.personID = 6 and f2.personID = 8 and f1.friendID = f2.friendID;

result:

personID 1    personID 2    common friend
6             8             2
6             8             3

although it's not a direct solution to your problem, you may base the solution here.