Sachin Vairagi Sachin Vairagi - 2 months ago 7
MySQL Question

mysql get friends from single table

I have a single table for friend request with status as pending , accepted or rejected, how to get my friends from this table ?
enter image description here

I followed this one but it is returning my own id i.e. 43

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)


I want to get user id 42 and 41 , please help!!! Thanks much.

Answer

Use IF condition in select statement to find friend_user_id.

select 
IF(@myid = requestFromUserId,requestToUserId,requestFromUserId) as friend_user_id 
from friends 
where (requestFromUserId = @myid or requestToUserId = @myid) and status = 'accepted'
Comments