get users who followed each other by MySQL query

Question

I want to get name, email, id of both users who followed each other and status equal to 'A'.

My friends table screenshot:

Note: In the above screenshot follower_id and following_id are both users table ids.

Answer 1

I achieved my goal by the following query:

SELECT 
    f1.id as f1_friendship_id, f1.follower_id as f1_user_id,
    u1.full_name as f1_full_name, u1.email as f1_email,
    f2.id as f2_friendship_id, f2.follower_id as f2_user_id,
    u2.full_name as f2_full_name, u2.email as f2_email
FROM 
    (friends f1 INNER JOIN users u1 ON f1.follower_id = u1.id ), 
    (friends f2 INNER JOIN users u2 ON f2.follower_id = u2.id)
WHERE 
    f1.follower_id = f2.following_id
    AND f1.following_id = f2.follower_id
    AND f2.id <> f1.id
    AND f1.status = 'A' AND f2.status = 'A';

In the above query, to segregate users those followed each other I used self-join and to append user info I used inner-join. I combined the joins.

Output: