CODEWITHSUNDEEP
sqlrelational-database
J V
J V

Reputation: 11946

Two way relationship in SQL with 2 tables and no duplicates

Assuming you have 2 tables: user and friends - you want to join all friends to the user you query.

The obvious answer is:

SELECT friend.uid
FROM `user`
JOIN friends ON user.uid = friends.user
JOIN user AS friend ON friends.friend = friend.uid
WHERE user.uid = $user_id

This works fine but it's a one-way relationship. To make this work I'd have to add 2 rows for every relationship.

On the other hand, this works both ways:

SELECT friend.uid
FROM `user` 
JOIN friends ON user.uid IN(friends.col1, friends.col2)
JOIN user AS friend ON
  friend.uid IN(friends.col1, friends.col2)
WHERE user.uid = $user_id AND friend.uid != user.uid

But this is makes use of multiple IN()s is not very clean and probably comes with a big performance hit.

Are there any other ways to do this?

Upvotes: 0

Views: 648

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 181067

You simply use UNION ALL and skip the join entirely, something like;

SELECT friends.col1 friend_uid FROM friends WHERE friends.col2 = $user_id
UNION ALL
SELECT friends.col2            FROM friends WHERE friends.col1 = $user_id

Upvotes: 1

Related Questions