How to add a column showing if a user follows another user?-CodePudding

I have a USER table which has the userId and the name of a user.

USER
_____________
userId | name

I also have a Followers table. Both columns are foreign keys of userId of the USER table above. It shows that userId follows other_userId.

FOLLOWER
_____________________
userId | other_userId

Question: How do i write a SQL statement that shows the following result?

EXPECTED QUERY RESULT
___________________________
userId | name | is_followed

It shows all users on the USER table with their id and name. But, it also shows a 3rd boolean column is_followed that shows if the specific userId 123 follows the user on that row or not.

What I have tried...

select
    id,
    name
from account a
left join (
  -- i tried a  bunch of things here till my head hurt..
) f on f.userId = id

CodePudding user response：

Assuming Postgres, I do not know MySQL.
A couple ways come quickly to mind. Basically what you are looking for is the existence of any row in the followers table with the same userid as the target (current) row of user table. Perhaps the easiest is just ask if that exists

with users (user_id, name) as 
     ( values (1,'user 1')
            , (2,'user 2')
            , (3,'user 3')
     ) 
     
   , followers ( user_id, other_userid) as 
     (values (1,2)
           , (3,1)
           , (1,3) 
     )
-- your query begins here 
select u.user_id
     , u.name
     , exists(select null 
                from followers f 
               where f.user_id = u.user_id
              ) is_followed 
  from users u 
 order by u.user_id;

You can get the same with the join you attempted, although I do not understand why you user the account table, unless what you are asking in not the complete issue, the trick being to look for Null/Not Null column from followers.

with users (user_id, name) as 
     ( values (1,'user 1')
            , (2,'user 2')
            , (3,'user 3')
     ) 
     
   , followers ( user_id, other_userid) as 
     (values (1,2)
           , (3,1)
           , (1,3) 
     )
-- your query begins here 
select distinct on (u.user_id) u.user_id
     , u.name
     , f.user_id is not null is_followed
  from users u
  left join followers f 
    on (  f.user_id = u.user_id) 
 order by u.user_id;

See demo here. In both cases the with clause is used just to supply data.

I caution against user as a table name. It is a reserved word for both Postgres and SQL Standard. While it would be accepted at best it leads to confusion. Further what happens when the predefined/reserved meaning is enforced?

CodePudding user response：

If I understand correctly a generic ansi-sql query would be

select u.UserId, u.name, 
  case when exists (
    select * from follower f
      where f.other_userId = u.userId
  ) then 1 else 0 end is_followed
from user u;

Also note from your description it should be correlating on other_userid otherwise you are showing the user follows not is followed.