Single Row Friendship Database Schema with getting userid from one column or the other

Question

I need help querying the friendID from a table. My table stores the user id of two members who are friends together.

But in order to store a "friendhship" b/w two members I would have to store two records like this:

friendshipID |  userID  |  friendID  
1            |  5       |  10
2            |  10      |   5

Yet, that seems heavy for the DB when we really only need to store the first record as that is sufficient as it contains both ids of both members.

However, the trouble comes when I want to query the records of the friends of ID=5. Sometimes the ID is in the userID column and other times it is in the friendID column.

This is the query I am using:

SELECT *
FROM friends
WHERE userID = '5'
   OR friendID = '5'

But what I want to do is something like this

SELECT 
if $userID=5 then userID as myfriend
else friendID=5 then friendID as myfriend
FROM friends WHERE userID='5' OR myfriendID='5'

Hope that makes sense. In the end I would like to have all the friends ID's of member #5 and not bring up results with #5 as the friend or user....but just his friends.

Answer 1

This query would return the Id value, and name, of the friends of #5 as shown in this SQL Fiddle Example

SELECT f.FriendId AS FriendId
   , u.Name AS FriendName
FROM FriendTable AS f
INNER JOIN UserAccount AS u ON f.FriendId = u.UserId
WHERE f.UserId = 5

UNION

SELECT f.UserId AS FriendId
   , u.Name AS FriendName
FROM FriendTable AS f
INNER JOIN UserAccount AS u ON f.UserId = u.UserId
WHERE f.FriendId = 5

The UNION will remove duplicates, making this query work for both a single record of friends, or the 2 record friendship you mention in the comment. You shouldn't need the 2 record friendship though, because there is no new information being stored in the second record that you cannot get from only having one record.