title: Nesting INNER JOINs with UNION calls in SQL - quite lost
body: <p>I have a <code>friends</code> table: <code>id</code>, <code>fid1</code>, <code>fid2</code> which represents a friendship between two users (bidirectional). </p>
  
  <p>and a <code>users</code> table: <code>fid</code>,<code>name</code></p>
  
  <p>I'd like to make a function or query that takes</p>
  
  <ol>
  <li>User id</li>
  </ol>
  
  <p>and returns</p>
  
  <ol>
  <li>list of friends (users.fid, users.name) which represent all of the friends which are friends with original user id</li>
  </ol>
  
  <p>I'm having trouble with nesting JOINs and UNIONs. I need the UNION to make sure that I get all friendships (but also don't get duplicates). This is what I've got so far:</p>
  
  <pre><code>SELECT fid1 as friends FROM friendships WHERE fid2 = 123456
  INNER JOIN users
  ON friendships.fid1 = users.fid
  
  UNION ALL (
  
  SELECT fid2 as friends FROM friendships WHERE fid1 = 123456
  INNER JOIN users
  ON friendships.fid2 = users.fid
  )
  </code></pre>
  
  <p>which is an error:</p>
  
  <pre><code>#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN users ON friendships.fid1 = users.fid UNION ALL ( SELECT fid2' at line 2
  </code></pre>
  
  <p>in general I'm having trouble with commands because I don't understand how SQL treats results - in normal programming languages I'd pass the result of the INNER JOINs to  the UNIONs and then apply that function. But I'm getting a bit lost here. Any thoughts?</p>