MySql query to check list of ids if they are friends with me?

So I have a list of ids(userId’s) that was found using a sql command that I want to check with my friends table to see if they are indeed friends with myself, a userid I will provide with, and have a column that will represent if they are and also group them if they are my friend and not my friend.

Example:

List of userids:
1
2
3

Friends table:
-------
userId           *the userid sending a friend request*
friendId         *the userid receiving the friend request*
relationshipId   *an unique id for the relationship*
initiated_by     *the userid initiating the friend request*
status           *whether or not the users are friends 'friends' or 'pending'*

sample friends table data

I tried creating a subquery that would first get the list of ids I want, and then tried to compare it with my friends table but couldn’t quite put it together because the friends table is not bidirectional, meaning each row represents a relationship between 2 people with a status friends or pending

Answer

Use a CASE expression to get the friend of a person (i.e. pick either the userid or the friendid from the friends table).

As of MySQL 8 you can use a WITH clause to get this readable:

with friends_of_user_1 as
(
  select case when userid = 1 then friendid else userid end as userid
  from friends
  where 1 in (userid, friendid)
  and status = 'friends'
)
, friends_of_user_2 as
(
  select case when userid = 2 then friendid else userid end as userid
  from friends
  where 2 in (userid, friendid)
  and status = 'friends'
)
select 
  userid,
  userid in (select userid from friends_of_user_2) as is_friend_of_user_2
from friends_of_user_1;

The same without WITH:

select 
  userid,
  userid in 
  (
    select case when userid = 2 then friendid else userid end as userid
    from friends
    where 2 in (userid, friendid)
    and status = 'friends'
  ) as is_friend_of_user_2
from 
(
  select case when userid = 1 then friendid else userid end as userid
  from friends
  where 1 in (userid, friendid)
  and status = 'friends'
) friends_of_user_1;