title: MySQL Query to retrieve relationship between you and a list of friends
body: <p>I would simply like to query my friends table for friends of a specific user, and then find out on that list of users, if they are my friends or not. The rows in the friends table is bidirectional, meaning that one row represents a relationship between 2 users.</p>
  
  <p>These are the tables im using currently to get the list of friends for a specific user:</p>
  
  <pre><code>friends
  -------
  userId           *the user sending a friend request*
  friendId         *the user receiving the friend request*
  relationshipId   *an id for the relationship*
  initiated_by     *the user initiating a friend request*
  status           *whether or not the users are friends 'friends' or 'pending'*
  </code></pre>
  
  <pre><code>users
  -------
  userId           *the userid of a user*
  username         *the username of a user*
  </code></pre>
  
  <p>The current query I had got the list of friends for a specific user but didn't give me the relationship between myself and the list of those friends for that specific user:</p>
  
  <pre><code>SELECT B.userId, B.username, A.relationshipId, A.initiated_by, A.status FROM (SELECT userId, relationshipId, initiated_by, status FROM friends WHERE (friendId= 'insert_userId' AND status = 'friends') UNION SELECT friendId, relationshipId, initiated_by, status FROM friends WHERE (userId= 'insert_userId' AND status = 'friends')) A INNER JOIN users B USING (userId)
  </code></pre>
  
  <p>Sample date in the friends table:
  <a href="https://i.stack.imgur.com/DmGxo.png" rel="nofollow noreferrer">friends table data</a></p>
  
  <p>Expected outcome would just be the userId's of the friends of the specific user and their relationship with me (via status column)</p>
  
  <p><a href="https://i.stack.imgur.com/DmGxo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DmGxo.png" alt="enter image description here"></a></p>