CODEWITHSUNDEEP
sqlpostgresql
Oleg Tarasenko
Oleg Tarasenko

Reputation: 9610

sql: How to get related data from the same table

I have kind of a newby question, please help

I have two tables:

users (id, username)

and

follows (follow, follower)

follow is person who is followed, follower - person who followed follow. Both follow and follower are ids from users table.

I need to find all usernames of people who are followed by me, and also to get the status if they are following me. And vise versa.

The most problematic for me is how to get if they (me) are following me back.

I am writing something like:

select users.id, users.username, f.follow from users 
left join follows f on f.follow = users.id where f.follower = 78;

and it gives me all users I am following but without information if they are following me back..

====

Update:

The query provided FuzzyTree works for me. But actually I have a related issue. What if I want to get all followers of user 78 for example, and to have follow button for all of them who are not followed by me (user 71)

Upvotes: 1

Views: 391

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32392

select users.id, users.username, f2.follow also_follows_follower
from users 
join follows f on f.follow = users.id 
left join follows f2 on f2.follower = users.id and f2.follow = f.follower
where f.follower = 78;

also_follows_follower will be null if they don't follow the follower

if you want to check another user just add another left join

select users.id, users.username, 
f2.follow also_follows_follower, f3.follow also_follows_me
from users 
join follows f on f.follow = users.id 
left join follows f2 on f2.follower = users.id and f2.follow = f.follower
left join follows f3 on f3.follower = users.id and f3.follow = 71
where f.follower = 78;

Upvotes: 2

PeterRing
PeterRing

Reputation: 1797

select users.id, users.username, f.follow, 
CASE 
WHEN EXISTS(SELECT 1 from follows f1 where f1.follow=78 and f1.follower=f.follow)
THEN 'Following ME' else 'Not' END as Folovingme
from users 
left join follows f on f.follow = users.id 
where f.follower = 78;

Upvotes: 3

Related Questions