MySQL: INNER JOIN

Question

I have a table that contains UserId and its friends id, for example:

----------------------------------------------
UserFriendsId    |    UserId    |    FriendId
----------------------------------------------
     1                  1              2
----------------------------------------------
     2                  1              3
----------------------------------------------
     3                  2              1
----------------------------------------------
     4                  2              3
----------------------------------------------

This table data shows that User-1 and User-2 are friends and also have frndship with User-3. Now I want to find common friends among UserId 1 and UserId 2, for example: In the mailing list, my query: User 1 and User 2 have 1 friend FriendId 3 in common.

For this, I used the SQL query for INNER JOIN:

SELECT t1.* 
  FROM userfriends t1 
 INNER JOIN userfriends t2 
    ON t1.FriendId = t2.FriendId 
 WHERE t1.UserId = 2

But do not return the desired result.

Answer 1

select * 
  from MyTable as A
 inner join MyTable as B 
    on     (A.UserID = 1 and B.UserID = 2)
       and (A.FriendID = B.FriendID)

edited by

Answer 2

Try this query (very similar to other published ones)

SELECT t1.UserID,T2.userID,T1.FriendID 
  FROM userfriends t1 
  JOIN userfriends t2 
    ON     (t1.FriendId = t2.FriendId )
       and (T1.userID <>T2.userID)
 WHERE t1.UserId = 2 
   and T2.userId=1

, , . , , T1.UserID , T2.userID:

SELECT t1.UserID,T2.userID,T1.FriendID 
  FROM userfriends t1 
  JOIN userfriends t2 
    ON     (t1.FriendId = t2.FriendId )
       and (T1.userID < T2.userID)

Answer 3

 SELECT table1.name, table2.salary
  FROM employee AS table1 INNER JOIN info AS table2 ON table1.name = table2.name;

Answer 4

Please, try

select u1.FriendId
from userfriends u1
inner join userfriends u2 on u1.FriendId = u2.FriendId
where u1.UserId = 1 and u2.UserId = 2