[SOLVED] Twitter style following-follower table in SQL-2

Twitter style following-follower table in SQL-2

Addition to this Twitter style following-follower table in SQL

In the upper example I was able to return the query for "if userB is following the other users who follows him"

I need a simpler thing now. I tried to modify the original code without any luck. I simply need "if userB is following or not any of the users , regardless if they follow him. Currently it returns null for people who dont follow that particular user.

This is the sample table for users

id      user_id     
1           userA     
2           userB     
3           userC      
4           userD      
5           userE

This is the sample table for follows

id      user_id     follower_id
1           userA       userB
2           userD       userB

I like to return a result that includes this follower/following state. For example for userB (he follows A and D but not C and E:

id      followedBy     areWeFollowing
1           userA       1
2           userB       0
3           userC       0
4           userD       1
5           userE       0

Thanks for your help!

arda

Solution

you can even take a count also from second table for this situation -

select id,user_id, ( select count(*) 
                       from follows 
                      where follows.user_id = users.user_id
                        and follows.follower_id = 'userB') as areWeFollowing
from users