Listing all friends and joining to the users table

Question

I have a database named 'friends' and it has columns 'user_id' and 'friend_id'. user_id is the invitor and friend_id the recipient. Please note that when a friendship is created I'm NOT making 2 records in the database like 1,2 and 2,1.

How to list all the friends of mine, considering that my users.user_id can vary between friends.friend_id and friends.user_id in the 'friends' table. Also how to join the query to the 'users' table to get the names of all my friends.

Answer 1

One option would be a union of two queries:

select u.name
    from friends f
        inner join users u
            on f.user_id = u.user_id
    where f.friend_id = @YourID
union
select u.name
    from friends f
        inner join users u
            on f.friend_id = u.user_id
    where f.user_id = @YourID