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?

Answers


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.


Need Your Help

Hide user control present in master page

c# asp.net user-controls hide master-pages

I have an user control in master page with id 'ucTopUser' and a button 'btnSub'.I need to hide the both control from my current aspx page.How can I do this?

Using $injector vs. direct DI in angularjs?

angularjs angularjs-injector

I am not seeing the difference (or pros/cons) to directly injecting my dependencies vs. using $injector in angularjs. Why would I do one or the other? Here is a sample of the two.