Reciprocal sql friends

Question

I saw several SO posts for mutual friends, but I structured my friends table in my db so that there are no duplicates, for example. (1,2) and not (2,1)

    Create Table Friends(
      user1_id int, 
      user2_id int
    );

and then a restriction to make sure userid1 is always less than userid2, for example 4 <5

Mutual sql friends with intro (Mysql)

I see suggestions that to search for common friends this can be found using the union, so this is what I have, but I think it is wrong, because if I read the data in my db with the actual result request, I get different results

select f1.user1_id as user1, f2.user1_id as user2, count(f1.user2_id) as 
mutual_count from Friends f1 JOIN Friends f2 ON 
f1.user2_id = f2.user2_id AND f1.user1_id <> f2.user1_id  GROUP BY
f1.user1_id, f2.user1_id order by mutual_count desc

Answer 1

There are three connection scenarios that I see.

1 -> 2 -> 3    (mutual friend id between other IDs)    
2 -> 3 -> 1    (mutual friend id > other IDs)    
2 -> 1 -> 3    (mutual friend id < other IDs)    

...

ON f1.user1_id IN (f2.user1_id, f2.user2_id)
OR f1.user2_id IN (f2.user1_id, f2.user2_id)
AND <not joining the row to Itself>

.

, .

(, )

SELECT u1, u2, COUNT(*) FROM
(
    SELECT f1.u1, f2.u2 FROM f1 INNER JOIN f2 ON f1.u2 = f2.u1 AND f1.u1 <> f2.u2
    UNION ALL
    SELECT f1.u1, f2.u1 FROM f1 INNER JOIN f2 ON f1.u2 = f2.u2 AND f1.u1 <> f2.u1
    UNION ALL
    SELECT f1.u2, f2.u2 FROM f1 INNER JOIN f2 ON f1.u1 = f2.u1 AND f1.u2 <> f2.u2
) all_combinations
GROUP BY u1, u2

. ( u1 u2)

( CASE) .