Getting a list of friend's names from MySQL

Question

I have a table named friends;

    friends
id   uid   fid
1     1     2   (1 is a friend of 2 and vice versa)
2     1     3   (1 is a friend of 3 and vice versa)
3     4     1   (1 is a friend of 4 and vice versa)
4     5     1   (1 is a friend of 5 and vice versa)

And a corresponding table for users;

  users
uid  name
1    mike
2    john
3    karl
4    mary
5    tony

This doesn't seem to do the trick:

SELECT name FROM users LEFT JOIN friends ON friends.uid=users.uid WHERE friends.uid='1' OR friends.fid='1'

What should my query be to get all the names of mike's friends?

what is data type of uid in Db – sandeep Mar 12 '12 at 13:10 — sandeep, Mar 12 '12 at 13:10

score 3 · Accepted Answer · answered Mar 12 '12 at 13:21

3

This should do it just fine with a single, easy to index, query;

SELECT name FROM users u
JOIN friends f 
  ON u.uid = f.uid OR u.uid = f.fid
WHERE (f.uid=1 OR f.fid=1) 
  AND u.uid<>1;

Demo here.

answered Mar 12 '12 at 13:21

Joachim Isaksson

176,943
25
281
294

There were a lot of answers but this is the one I tried. Worked like a charm. Thanks, @Joachim. +1 for introducing sqlfiddle to me. – MikeS Mar 12 '12 at 13:32
Would you be also able to help me out here, http://stackoverflow.com/questions/9663607/what-are-ways-to-get-mutual-friends-in-a-1-2-2-1-friendship-scenario/9664094#9664094 – MikeS Mar 12 '12 at 13:34

score 2 · Answer 2 · answered Mar 12 '12 at 13:14

Untested:

SELECT name from friends LEFT JOIN users on users.uid=friends.fid where friends.uid=1 UNION 
SELECT name from friends LEFT JOIN users on users.uid=friends.uid where friends.fid=1

This may look a little strange if anyone is ever friends with themselves.

score 2 · Answer 3 · answered Mar 12 '12 at 13:17

try one of these:

SELECT a.uid as UserID,
       a.`Name` as UserName,
      c.`Name as FriendsName
FROM users a LEFT JOIN friends b on a.uid = b.uid
    LEFT JOIN users c on b.fid = c.uid

OR

SELECT a.uid as UserID,
       a.`Name` as UserName,
      GROUP_CONCAT(c.`Name`) as FriendsList
FROM users a LEFT JOIN friends b on a.uid = b.uid
    LEFT JOIN users c on b.fid = c.uid
GROUP BY a.uid

score 1 · Answer 4 · edited May 23 '17 at 11:57

1

As in your prequel question, you need to cover both foreign keys to the user table to get all his friends:

SELECT users.*
FROM  (
    SELECT uid FROM friends WHERE fid = 1
    UNION ALL
    SELECT fid FROM friends WHERE uid = 1
    ) f
JOIN   users USING (uid)

edited May 23 '17 at 11:57

Community

1
1

answered Mar 12 '12 at 13:13

Erwin Brandstetter

605,456
145
1,078
1,228

score 0 · Answer 5 · answered Mar 12 '12 at 13:11

0

Switch friends and users in your query and I think you'll get what you want.

In other words:

SELECT name FROM friends LEFT JOIN users ON friends.uid=users.uid WHERE friends.uid='1' OR friends.fid='1'

answered Mar 12 '12 at 13:11

Matt Burland

44,552
18
99
171

score 0 · Answer 6 · answered Mar 12 '12 at 13:11

0

I think this is right SELECT name FROM users LEFT JOIN friends ON friends.uid=users.uid WHERE friends.uid=1 OR friends.fid=1

answered Mar 12 '12 at 13:11

sandeep

2,244
1
23
38

Getting a list of friend's names from MySQL

6 Answers6

Linked