I have two tables representing users and their friends: users and friendships
usershasidandnamefriendshipshasid,friend1_idandfriend2_id
By searching a user's name, I want to get their associated friends' names.
-
did you test the answer that you accepted? Check this: db-fiddle.com/f/oD5vzNH9H7tXQMR4HJDint/2 it does not return your expected results.forpas– forpas2020-06-10 18:57:39 +00:00Commented Jun 10, 2020 at 18:57
-
@forpas it works as expected. The relationships are one way.Aerd6154– Aerd61542020-06-11 12:14:26 +00:00Commented Jun 11, 2020 at 12:14
Add a comment
|
2 Answers
You can try this:
SELECT u2.name from users u1
INNER JOIN users u2 ON u1.name = [your_name_to_search]
INNER JOIN friendships fs
ON u2.id = fs.friend2_id AND u1.id = fs.friend1_id
Explanation:
I used 2 joins in this approach: the first users self join will assure you take the id corresponding to [your_name_to_search], and then the second join with friendships will extract the names of friend_2_id matching with the friend_1_id taken from the first join.
You can test this approach on this db-fiddle
Comments
Join friendships to 2 copies of users.
The 1st copy will be filtered for the user name that you search for and the 2nd will return all the user's friends:
select uf.name
from users u
inner join friendships fs on u.id in (fs.friend1_id, fs.friend2_id)
inner join users uf on uf.id in (fs.friend1_id, fs.friend2_id) and uf.id <> u.id
where u.name = ?
Replace ? with the name of the user whose friends you want in the results.
