使用MySql JOINS在表中查找常用值

Question

This is my way to check for mutual friends in my database.

For ex. if user one has friend user: (1, 2, 3, 4) and user two has friend user: (2, 4, 5) I want to show (2 and 4).

Is there any better way to do this using MYSQL JOINS for ex.?

I have a users table whith user_id column which contains all users id and friends table with user_one and user_two columns.

$user_one_fr = $db->query("select * from `friends` where `user_one` = '$session_user_id' or `user_two` = '$session_user_id'");
$user_two_fr = $db->query("select * from `friends` where `user_one` = '$user_id' or `user_two` = '$user_id'");

    while ($loop_user_one_fr = mysqli_fetch_assoc($user_one_fr)) {
        if($loop_user_one_fr["user_one"] == $session_user_id)
            $user_one_fr_array[] = $loop_user_one_fr["user_two"];
        else 
            $user_one_fr_array[] = $loop_user_one_fr["user_one"];
    }


    while ($loop_user_two_fr = mysqli_fetch_assoc($user_two_fr)) {
        if($loop_user_two_fr["user_one"] == $user_id)
            $user_two_fr_array[] = $loop_user_two_fr["user_two"];
        else 
            $user_two_fr_array[] = $loop_user_two_fr["user_one"];

    }

    $mutual_fr =  array_intersect($user_one_fr_array, $user_two_fr_array);

    foreach ($mutual_fr as $fr)
    echo $fr . ', ';

Answer 1

You can do it this way

SELECT f1.friend mutual_friend
  FROM
(
  SELECT IF(user_one = :user_id, user_two, user_one) friend
    FROM friends
   WHERE :user_id IN (user_one, user_two)
) f1 JOIN 
(  SELECT IF(user_one = :session_user_id, user_two, user_one) friend
    FROM friends
   WHERE :session_user_id IN (user_one, user_two)
) f2 
    ON f1.friend = f2.friend;

or

SELECT IF(user_one IN(:user_id, :session_user_id), user_two, user_one) mutual_friend
  FROM friends
 WHERE :user_id IN (user_one, user_two)
    OR :session_user_id IN (user_one, user_two)
 GROUP BY IF(user_one IN(:user_id, :session_user_id), user_two, user_one)
HAVING COUNT(*) > 1;

Sample output (in both cases):

| MUTUAL_FRIEND |
|---------------|
|             2 |
|             4 |

Here is SQLFiddle demo