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*

Answer 2

Why not simply? Unless you need fields from users which you have not indicated.

SELECT idFirst
      ,idSecond 
  FROM Friends
 WHERE IdFirst = *LOGGED USER ID*
    OR IdSecond =*LOGGED USER ID*

This means you don't have to have mirrored friendships--and in fact you should not.

EDIT: if you do want the user for the friends you can do it without a union as:

SELECT * 
  FROM users U
 WHERE UserID <> *LOGGED USER ID*
   AND EXISTS(
               SELECT 1
                 FROM Friends 
                 WHERE (IdFirst = *LOGGED USER ID* AND IdSecond = UserID)
                    OR (IdSecond =*LOGGED USER ID* AND IdFirst = UserID)
             )  

I'm not sure it better than @BarbaraLaird 's. Though the execution plan looks simpler here http://sqlfiddle.com/#!9/da447/13

Answer 3

First of all doing left join while filtering from right table is nonsense because left join becomes inner join. Second, you can do this with joins, no need for union:

select case when u1.Id = @currentUser then u1.Id else u2.Id end as Id,
       case when u1.Id = @currentUser then u1.Name else u2.Name end as Name,
from Friends f
join Users u1 on f.IdFirst u1.Id
join Users u2 on f.IdSecond u2.Id
where u1.Id = @currentUser or u2.Id = @currentUser