I have two tables called users and fans.
The users table looks like this:
id | name
---------
1 | John
2 | Mark
3 | Bill
4 | Steve
And this is the fans table:
fan | user
1 | 2
2 | 3
2 | 4
"Fan" is the user who is following the user and "user" is the one who is being followed. Now If we take John (id 1) as the current user, and given that John is following Mark (id 2), How can we get the following of Mark and the following of everyone whom John is following (In this case Mark is following user 3 and 4 so id 3 and 4 should be returned from the db)? I tried doing this with the IN operator but I am not sure it would scale well with more entries. Any help would be appreciated.
>Solution :
If I understand correctly, you can try to join users and fans to find who is followed user then do join
SELECT *
FROM users u1
WHERE EXISTS (
SELECT 1
FROM users uu
INNER JOIN fans f
ON uu.id = f.fan
WHERE uu.id = 1 AND f.user = u1.id
)
or using self-join user twice
SELECT u1.*
FROM users u1
INNER JOIN users uu
INNER JOIN fans f
ON uu.id = f.fan AND f.user = u1.id
WHERE uu.id = 1