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?
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.
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'
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
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)
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.
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