Nice query to get my friends

Question

my table has this structure:

 User_ID | Followed_ID

Example row:

 33 | 36

Means that user 33 follows user 36.

If exists a record like

 36 | 33

then the two users are considered friends. What's the cleanest way to get all friends of(for example) user 36? Thanks.

Answer 1

It can be done with a simple join (assuming the (user_id, follower_id) combination has a unique constraint):

SELECT 
    f1.followed_id AS friend_id
FROM
    follow AS f1
  JOIN
    follow AS f2
      ON  f1.user_id = f2.followed_id
      AND f1.followed_id = f2.user_id
WHERE
    f1.user_id = 36 ;