SQL Select friends query

Question

I am trying to select the friends of the current user, using a query that returns a list of friends for table 1 row per friendship.

I have a User and a Friends Table:

User(UserID, Username)

Friends(IdFirst, IdSecond)

Assuming i have the following users: (1, 'Alex'), (2, 'Ana'), (3, 'Daniel') and the following friendships: (1, 2), (1,3), (2,3)

Up until now I have been using this query:

SELECT * FROM User U
LEFT JOIN Friends F
ON U.IdUser = F.IdSecond
WHERE F.IdFirst = *LOGGED USER ID*

And it only works if I have mirrored friendships, example: (1, 2) (2, 1) (1, 3) (3,1) and I only want to have a single pair for each friendship. If use the above query I only get the list of friends for IdFirst.

I hope I make sense, thank you!

Answer 1

How about a union? http://sqlfiddle.com/#!9/da447/7

SELECT * FROM users U
  LEFT JOIN friends F
  ON U.userid = F.idsecond
  WHERE F.idfirst = *LOGGED USER ID*
UNION
SELECT * FROM users U
  LEFT JOIN friends F
  ON U.userid = F.idfirst
  WHERE F.idsecond = *LOGGED USER ID*