Single Row Friendship database schema with user ID from one column or another

Question

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

But in order to save the "friendhship" b / w of two members, I would have to store two entries, for example:

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

However, this seems difficult for the database, when we really only need to save the first record, because this is enough, because it contains both identifiers of both members.

However, the problem occurs when I want to request friend records ID = 5. Sometimes the identifier is in the userID column, and sometimes it is in the friendID column.

This is the query I'm using:

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

But I want to do 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 this makes sense. In the end, I would like to have all friends of Participant ID No. 5 and not give C # 5 results as a friend or user .... but only his friends.

Answer 1

This query will return the Id value and friends of friends # 5 as shown in this SQL Script 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 

UNION will remove duplicates, making this query work for both a single friend record and two friendship records you mentioned in a comment. You should not need the friendship of the two records, because in the second record there is no new information from which you cannot get only one record.