A rather complicated join

Question

It is not actually that complicated, I might have misled you with the title a little. Basically what I have is 2 tables

CREATE TABLE `friendships` (
 `sender` int(11) unsigned NOT NULL,
 `recipient` int(11) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

and users from which only the id column is involved in this problem.

friendships holds connections between people. What I am trying to do is get all friends of user n and information about them from the users table. The problem is that in friendships user n may be recipient OR sender so I need to get information on respectively the other person from the friendships record.

I don't even have the slightest clue on how I'm supposed to construct this join, is it even possible?

I guess it will go something like

SELECT * 
FROM friendships LEFT JOIN users ON ???=users.id WHERE recipient=n OR sender=n

Answer 1

SELECT * 
FROM friendships f
LEFT JOIN users s ON s.id = f.sender 
LEFT JOIN users r ON r.id = f.recipient 
WHERE n in (f.recipient, f.sender)