CODEWITHSUNDEEP
sqlsocial-networking
Cosmin-Alexandru Ciocirlan
Cosmin-Alexandru Ciocirlan

Reputation: 101

SQL Select friends query

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!

Upvotes: 0

Views: 2701

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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

Upvotes: 0

Karl Kieninger
Karl Kieninger

Reputation: 9129

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

Upvotes: 0

Barbara Laird
Barbara Laird

Reputation: 12717

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*

Upvotes: 5

Related Questions