SQL query to find all users, User 'A' follows and then check if another User 'B'-CodePudding

I want to display all users data, who User 'A' is following. And then further check if User 'B' is also following some users of User 'A'.

I managed to get al users data, who User 'A' is following. But don't understand how to query for the second condition.

Here is my Fiddle link with an example: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=29a7d1e29f794a8f18a89fe45c06eaa9

CodePudding user response：

You can try to let your User 'B' in a subquery then do OUTER JOIN

SELECT u.*,
       IF(friend_id IS NULL,0,1) amIfollowing
FROM users u
LEFT JOIN (
   Select friend_id 
   from friends 
   where user_id = 5
) f ON f.friend_id = u.id
WHERE u.id IN (SELECT f.friend_id
               FROM friends f
           WHERE f.user_id = 1)
ORDER BY u.id

sqlfiddle

If I understand correctly you can try to use only one subquery for friends and then use the condition aggregate function to get the result.

SELECT u.id,
       u.image_width,
       MAX(CASE WHEN f.user_id = 5 THEN 1 ELSE 0 END) amIfollowing
FROM users u
JOIN (
   Select friend_id,user_id 
   from friends 
   where user_id IN (1,5)
) f ON f.friend_id = u.id
GROUP BY u.id,
       u.image_width
ORDER BY u.id

CodePudding user response：

You could use exists here to check if the corresponding IDs exist:

SELECT *, 
  case when exists (
    select * from friends f 
    where f.friend_id = u.id and f.user_id = 5
  ) then 1 else 0 end amIfollowing
FROM users u    
WHERE u.id IN (SELECT f.friend_id
               FROM friends f
           WHERE f.user_id = 1);

Example Fiddle

CodePudding user response：

Looks like a JOIN will do, with distinct

SELECT distinct u.*, (f2.user_Id is not null) amIfollowing
FROM users u 
JOIN friends f ON u.id = f.friend_id
LEFT JOIN friends f2 on f2.friend_id = f.friend_id and f2.user_id = 5
WHERE f.user_id = 1
ORDER BY u.id