Get following of users whom I follow in SQL-CodePudding

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.

CodePudding user response：

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

sqlfiddle