title: Simplify MySQL query
body: <p>I have a MySQL query that I used to use to return mutual friends between two users, but now that I am recoding my website, I am trying to simplify this code, or at least make it better. </p>
  
  <p>So here's my code below to check mutual friends:</p>
  
  <pre><code>SELECT a.friendID
  FROM
  (SELECT CASE WHEN userID = $id
      THEN userID2 
      ELSE userID 
      END AS friendID 
  FROM friends 
  WHERE (userID = $id OR userID2 = $id) 
      AND state='1'
  ) a
  
   JOIN
  (SELECT CASE WHEN userID = $session
      THEN userID2 
      ELSE userID 
      END AS friendID 
  FROM friends 
  WHERE (userID = $session OR userID2 = $session) 
      AND state='1'
  ) b
  
  ON b.friendID = a.friendID
  </code></pre>
  
  <p>My table is set up like this:</p>
  
  <pre><code>   userID -- userID2 -- state
        1 ------- 2 ------- 1
        2 ------- 3 ------- 1
        1 ------- 3 ------- 0
  </code></pre>
  
  <p>(sorry, I don't know how to do the pretty database structure design, so if someone could edit that for me...)</p>
  
  <p>but for the above, when user 1 is on user 3's profile, since user 1 and user 2 are friends, and user 2 and user 3 are friends, but user 1 and user 3 are not, it should return user 2 as a mutual friend. (state 1 means friendship accepted, state 0 means friendship pending, so only if state is 1 should it be counted as a friend)</p>
  
  <p>Also note that userID and userID2 can be in any order, it depends on who requests who as a friend, so like the above query does, I need to also have the "friendID" returned, as the above query does right. </p>