CODEWITHSUNDEEP
mysql
bhutchins1a
bhutchins1a

Reputation: 33

How to use MySQL to find users who follow each other

I thought this was going to be easy until I tackled it.

I'm trying to write a query that will produce a list of users who follow each other (like tiktok). It's not too hard to get the list of users who follow each other but my query shows both ways.

For example, the data look like:

follower_id     followee_id
     1               2
     1               3
     1               4
     2               1
     2               3
     2               4
     3               1
     3               2
     3               4
     4               2
     4               3

Using the above data, the output should be:

follower_id      followee_id
     1               2
     1               3
     2               3
     2               4
     3               4

Note that everybody follows everybody except 4 doesn't follow 1.

The table structure is below

CREATE TABLE follows (
    follower_id INT NOT NULL,
    followee_id INT NOT NULL
);

The INSERT statement:

INSERT INTO follows(follower_id, followee_id) 
VALUES(1,2), (1,3), (1,4), (2,1), (2,3), (2,4), (3,1), (3,2), (3,4), (4,2), (4,3);

I think a self-join can get the users who follow each other, but I get twice as many rows as I want.

mysql> SELECT
    -> f1.follower_id AS f1_follower,
    -> f1.followee_id AS f1_followee,
    -> f2.follower_id AS f2_follower,
    -> f2.followee_id AS f2_followee
    -> FROM follows f1
    -> JOIN follows f2
    -> ON f1.follower_id = f2.followee_id
    -> AND f1.followee_id = f2.follower_id
    -> ORDER BY f1.follower_id, f1.followee_id;
+-------------+-------------+-------------+-------------+
| f1_follower | f1_followee | f2_follower | f2_followee |
+-------------+-------------+-------------+-------------+
|           1 |           2 |           2 |           1 |
|           1 |           3 |           3 |           1 |
|           2 |           1 |           1 |           2 |
|           2 |           3 |           3 |           2 |
|           2 |           4 |           4 |           2 |
|           3 |           1 |           1 |           3 |
|           3 |           2 |           2 |           3 |
|           3 |           4 |           4 |           3 |
|           4 |           2 |           2 |           4 |
|           4 |           3 |           3 |           4 |
+-------------+-------------+-------------+-------------+
10 rows in set (0.00 sec)

In the first row, we see that 1 and 2 follow each other, but the third row also shows that 1 and 2 follow each other, just the reciprocal of the first row. Same goes for rows 2 and 6, rows 4 and 7, rows 5 and 9, and rows 8 and 10. So I really just want to show rows 1, 2, 4, 5, and 8 (ie., only the non-reciprocal rows). Reciprocal probably isn't the right word - the assumption in the output will be that 1, 2 means "users 1 and 2 follow each other."

I'm probably way over-thinking this one but any and all help will be greatly appreciated!

Upvotes: 0

Views: 133

Answers (1)

user1191247
user1191247

Reputation: 12998

Your original query is fine you just need the additional predicate to only return one version of the relationship -

SELECT f1.*
FROM follows f1
JOIN follows f2 ON f1.follower_id = f2.followee_id AND f1.followee_id = f2.follower_id
WHERE f1.follower_id < f1.followee_id;

Upvotes: 2

Related Questions