CODEWITHSUNDEEP
sqloracle-database
Ryan Collings
Ryan Collings

Reputation: 21

List all friends for a userid SQL query

I have 2 tables a user table (user_id, fname, lname, dob, etc) and a are_friends table (userA_id, userB_id). I have been trying to do this query for a while now, I need it to list all friends for a user_id.

What I have got so far, 

SELECT
  U.user_id,
  U.fname,
  U.lname 
FROM are_friends A, user U
WHERE
  A.user_id = U.user_id
  AND (
    A.user_id = 1
    OR A.user_id IN (SELECT userB_id FROM are_friends WHERE userA_id = 1)
  );

Any help will be much appreciated.

Upvotes: 2

Views: 274

Answers (1)

Aiias
Aiias

Reputation: 4748

Try using an INNER JOIN like this:

SELECT u2.user_id, u2.fname, u2.lname
FROM user u
INNER JOIN are_friends f ON f.userA_id = u.user_id
INNER JOIN user u2 ON u2.user_id = f.userB_id
WHERE u.user_id = 1

You can change the WHERE clause to specifically get the friends of another user id.

Upvotes: 3

Related Questions