title: MySQL Query to find friends and number of mutual friends
body: <p>I have looked through the questions but I cant find anything that does exactly what I need and I can't figure out how to do it myself.</p>
  
  <p>I have 2 tables, a user table and a friend link table. The user table is a table of all my users:</p>
  
  <pre><code>    +---------+------------+---------+---------------+
      | user_id | first_name | surname |     email     |
      +---------+------------+---------+---------------+
            1         joe       bloggs    joe@test.com
            2         bill      bloggs    bill@test.com
            3         john      bloggs    john@test.com
            4         karl      bloggs    karl@test.com
  </code></pre>
  
  <p>My friend links table then shows all relationships between the users, for example:</p>
  
  <pre><code>    +--------=+---------+-----------+--------+
      | link_id | user_id | friend_id | status |
      +---------+---------+-----------+--------+
         1         1          3           a
         2         3          1           a
         3         4          3           a
         4         3          4           a
         5         2          3           a
         6         3          2           a
  </code></pre>
  
  <p>As a note the a in the status column means approved, there could also be r(request) and d(declined).</p>
  
  <p>What I want to do is have a query where if a user does a search it will bring back a list of users that they are currently not already friends with and how many mutual friends each user has with them.</p>
  
  <p>I have managed to get a query for all users that are currently not friends with them. So if the user doing the search had the user id of 1:</p>
  
  <pre><code>SELECT u.user_id,u.first_name,u.surname
  FROM users u
      LEFT JOIN friend_links fl
          ON u.user_id = fl.user_id AND 1 IN (fl.friend_id)
  WHERE fl.friend_id IS NULL
  AND u.user_id != 1
  AND surname LIKE 'bloggs'
  </code></pre>
  
  <p>How then do I have a count of the number of mutual friends for each returned user?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Just as an edit as I don't think I am being particularly clear with my question.</p>
  
  <p>The query that I currently have above will produce the following set of results:</p>
  
  <pre><code>    +---------+------------+---------+
      | user_id | first_name | surname |
      +---------+------------+---------+
            2         bill      bloggs
            4         karl      bloggs
  </code></pre>
  
  <p>Those are the users matching the surname bloggs that are not currently friends with joe bloggs (user id 1).</p>
  
  <p>Then I want to have how many mutual friends each of these users has with the user doing the search so the returned results would look like:</p>
  
  <pre><code>    +---------+------------+---------+--------+
      | user_id | first_name | surname | mutual |
      +---------+------------+---------+--------+
            2         bill      bloggs     1
            4         karl      bloggs     1
  </code></pre>
  
  <p>Each of these returned users has 1 mutual friend as joe bloggs (user id 1) is friends with john bloggs and john bloggs is friends with both returned users.</p>
  
  <p>I hope this is a bit more clear.</p>
  
  <p>Thanks.</p>