MySQL: INNER JOIN

Question

I have a table which contains UserId & his Friends Id like:

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

This table data shows that User-1 & User-2 are friend & they also have frndship with User-3. Now I want to find common friend(s) among UserId 1 & UserId 2 for eg: In sentance my query is: User 1 & User 2 have 1 common Friend FriendId 3.

For this I used SQL query for INNER JOIN:

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

But not return required 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

Answer 2

Please try this

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

Answer 3

Try this query (very similar to th others posted)

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

Beware that this reports every friendship-chain twice once you remove the absolute IDs. To avoid that I ask that T1.UserID be smaller than 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 4

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