Q:

sql - fetch all user friends

I wish to query 3 different tables.  

My approach would be getting the the friends from FRIENDS TABLE.  So, the output would be: 

I tried having this solution  
SELECT users.firstname, users.lastname, 
users.screenname, profile.profile_pic, friends.friend_id
FROM website.users users
INNER JOIN website.friends friends ON (users.user_id = friends.friend_id)
LEFT  JOIN profile ON (friends.friend_id = profile.user_id)
WHERE (friends.user_id = "12052822" or friends.friend_id = "12052822")

Problem is when it matches one record, it outputs the the column "friend.user_id".  That's not what I want to do!  If the user is logged in, a query will fetch all his friends... How can I do that in an easier and better way? 

A:

The id and user_id columns in the users table are at least confusing?   Why two 'identifier' columns?   Which of the two joins with friend.user_id?   Is it the same column that joins with friend.friend_id?   And is the objective to display all the names where the friends.user_id is the user who just logged in, or all the names where the friends.friend_id is the user who just logged in, or both? 
Until further clarification is forthcoming, I will assume the result set should include both lists of friends: the people who have been friended by the user and the people who have friended the user.   I will also assume that the users.user_id column is the joining column. 
We can write this easily as a UNION, noting that a UNION will eliminate duplicate entries for the case where the user who logged in has friended a user who has also friended the user who logged in (so there are two entries in the friends table with the same pair of values in user_id and friend_id. 
Going in for TDQD (Test Driven Query Design), we can write (assuming the newly logged in user is user_id = 12052822):
Users friended by user 12052822
SELECT u.user_id, u.firstname, u.lastname, u.screenname, p.profile_pic
  FROM website.friends AS f
  JOIN website.users   AS u ON f.friend_id = u.user_id
  JOIN website.profile AS p ON p.user_id   = u.user_id
 WHERE f.user_id = 12052822;

Users who have friended user 12052822
SELECT u.user_id, u.firstname, u.lastname, u.screenname, p.profile_pic
  FROM website.friends AS f
  JOIN website.users   AS u ON f.user_id = u.user_id
  JOIN website.profile AS p ON p.user_id = u.user_id
 WHERE f.friend_id = 12052822;

Both sets of users
SELECT u.user_id, u.firstname, u.lastname, u.screenname, p.profile_pic
  FROM website.friends AS f
  JOIN website.users   AS u ON f.friend_id = u.user_id
  JOIN website.profile AS p ON p.user_id   = u.user_id
 WHERE f.user_id = 12052822;
UNION
SELECT u.user_id, u.firstname, u.lastname, u.screenname, p.profile_pic
  FROM website.friends AS f
  JOIN website.users   AS u ON f.user_id = u.user_id
  JOIN website.profile AS p ON p.user_id = u.user_id
 WHERE f.friend_id = 12052822;

You can test and debug each of the component queries separately, and then combine them to create the final answer.