CODEWITHSUNDEEP
mysqlsqljoinselectleft-join
WebDiva
WebDiva

Reputation: 171

Get following of users whom I follow in SQL

I have two tables called users and fans.
The users table looks like this:

id | name
---------
1  | John
2  | Mark
3  | Bill
4  | Steve
5  | Jeff

And this is the fans table:

fan | user
1   | 2
1   | 5
2   | 3
2   | 4
2   | 5

"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 everyone whom John is following, but not the ones whom John is already following (In this case John is following Mark. Mark is following user 3, 4 and 5 but only 3 and 4 should be returned because John is already following user 5.)? 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.

Upvotes: 2

Views: 516

Answers (2)

forpas
forpas

Reputation: 164099

You must join multiple copies of fans:

SELECT f1.user
FROM fans f1 
INNER JOIN fans f2 ON f2.user = f1.fan 
LEFT JOIN fans f3 ON f3.fan = f2.fan AND f3.user = f1.user 
WHERE f2.fan = ? AND f3.fan IS NULL;

If you want the names of the users also:

SELECT u.*
FROM users u
INNER JOIN fans f1 ON f1.user = u.ID
INNER JOIN fans f2 ON f2.user = f1.fan 
LEFT JOIN fans f3 ON f3.fan = f2.fan AND f3.user = f1.user 
WHERE f2.fan = ? AND f3.fan IS NULL;

Change ? to the id of the user that you want.

See the demo.

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

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

Upvotes: 1

Related Questions