title: Joining SELECT variable into different table columns | follow suggestion
body: <p>I'm trying to join the result from one specific SELECT variable into a a different colum from another table.</p>
  
  <p>user_friends table:</p>
  
  <pre><code>+-------------------------------------------+
  |    friend_id | friend_one | friend_two    |
  +-------------------------------------------+
  </code></pre>
  
  <p>users table:</p>
  
  <pre><code>+------------------+
  |   uiD | username |
  +------------------+
  </code></pre>
  
  <p>The following query retrieves random friends that my followers are following and suggests them to me, currently it's only retriving their uiD(id).
  I'm unsure of how to join these two tables together and get the GROUP BY possible_friend below to connect to the users table.</p>
  
  <pre><code>$sth = $this-&gt;db-&gt;prepare("
                              SELECT friend_two AS possible_friend
                              FROM user_friends
                              WHERE friend_one IN (SELECT friend_two FROM user_friends WHERE friend_one = :uiD) 
                              AND friend_two NOT IN (SELECT friend_two FROM user_friends WHERE friend_one = :uiD)
                              AND NOT friend_two = :uiD
                              GROUP BY possible_friend
                              ORDER BY RAND()
                              ");
  $sth-&gt;execute(array(':uiD' =&gt; $uiD));
  
  $data = $sth-&gt;fetch();
  return $data;
  </code></pre>
  
  <p>As an example, I'm user 10.</p>
  
  <p>uiD = 10 : Username : Jonathan</p>
  
  <p>uiD = 20 : Username : Gabriel</p>
  
  <p>uiD = 30 : Username : Lisa</p>
  
  <p>uiD = 40 : Username : Emily</p>
  
  <p>I'm logged in as Jonathan, that is following Gabriel, but Jonathan is not following Lisa nor Emily.
  Then it should show on a div.</p>
  
  <p>Follow Suggestions:
  Emily or Lisa</p>
  
  <p>With the query above, I'm just able to get their ID's randomly but I'm unable to join the Username from the users table with the possible_friend from the user_friends table.</p>
  
  <p>I've tried the following.</p>
  
  <pre><code>    $sth = $this-&gt;db-&gt;prepare("
                                  SELECT F.friend_two AS possible_friend, U.username
                                  FROM user_friends F, users U
                                  WHERE F.friend_one IN (SELECT friend_two FROM user_friends WHERE friend_one = :uiD) 
                                  AND F.friend_two NOT IN (SELECT friend_two FROM user_friends WHERE friend_one = :uiD)
                                  AND NOT F.friend_two = :uiD
                                  GROUP BY possible_friend
                                  ORDER BY RAND()
                                  ");
      $sth-&gt;execute(array(':uiD' =&gt; $uiD));
  
      $data = $sth-&gt;fetch();
      return $data;
  </code></pre>
  
  <p>but it just returns another username, since I'm unable to connect the U.username with the possible_friend.</p>
  
  <p><strong>UPDATE VERSION - WORKING VERSION :</strong></p>
  
  <pre><code>    SELECT F.friend_two AS possible_friend, U.username, U.uiD, U.photo
      FROM user_friends F, users U
      WHERE F.friend_one IN (SELECT friend_two FROM user_friends WHERE friend_one = :uiD) 
      AND F.friend_two NOT IN (SELECT friend_two FROM user_friends WHERE friend_one = :uiD)
      AND NOT F.friend_two = :uiD
      AND U.uiD NOT IN (SELECT friend_two FROM user_friends WHERE friend_one = :uiD) 
      AND U.photo NOT IN (SELECT friend_two FROM user_friends WHERE friend_one = :uiD) 
      GROUP BY possible_friend
      ORDER BY RAND()
  </code></pre>