CODEWITHSUNDEEP
mysql
JP.
JP.

Reputation: 564

MySQL friends table query

I have a friends table set up with columns like this:

requester_user_id //the user initiating the request
requested_user_id //the user responding to the request
established //bool, true is 'these two are friends'

I have some other columns, but they are not important for this question.

What I need is a query that will get a list of all of your friend IDs. The thing is, your ID might be in requester OR requested, depending on who initiated the request.

I feel like there has got to be a simple way, but I can not figure it out without doing multiple ifs.

Upvotes: 0

Views: 351

Answers (2)

user359040
user359040

Reputation:

One way:

select distinct 
       case requester_user_id 
            when @myid then requested_user_id 
            else requester_user_id 
       end id
from friends
where @myid in (requester_user_id, requested_user_id)

Upvotes: 2

Byron Whitlock
Byron Whitlock

Reputation: 53921

SELECT distinct id from (
SELECT requested_user_id as id FROM friends where requester_user_id = @myid
UNION
SELECT requester_user_id as id FROM friends where requested_user_id = @myid
)

Upvotes: 1

Related Questions