Suppose I want to keep who follows who in a simple table that has two fields ;

UserId and FollowedId and I have a simple data set like ;

I just use SELECT*FROM Followers WHERE FollowedId = 100 when I want to list all followers of the user with ID 100.

However, I want the following information in my followers list when I query ;

Who I follow among those following me.

I don't directly want who I follow and who follows like an intersection.

I want the result look like following when I query the follower list of a user with ID 100;

I can do it using another query but just wanted to ask the most efficient way of doing it ?

Having another column to keep this bidirectional relation ? Make another query once I get the follower list to see which ones among them I follow ?

CodePudding user response：

You could use an intersect within a CTE to get the userids, and then Case Expression for your third column:

with follow_back as (
  select userid, followedid
  from my_data
  where followedid = 100
  intersect
  select followedid, userid
  from my_data)
select userid, followedid,
case 
  when userid in (select userid from follow_back) then 'true' else 'false' end DoIFollowBack
from my_data
where followedid = 100

Output:

| userid | followedid | DoIFollowBack|
| ------ | ---------- | ------------ |
| 1      | 100        | false |
| 2      | 100        | true  |
| 3      | 100        | false |
| 4      | 100        | false |
| 5      | 100        | true  |
| 6      | 100        | false |

CodePudding user response：

I believe you can accomplish this with a self-join using a left outer join and evaluating the success of the join condition:

SELECT
  f1.*, f2 is not null as DoIFollowBack
FROM
  Followers f1
  left join Followers f2 on
    f2.FollowedId = f1.UserId and
    f1.FollowedId = f2.UserId
WHERE
  f1.FollowedId = 100