Взаимные друзья sql со вступлением (Mysql)

У меня две таблицы

таблица пользователей:

id|name 

user_relationships

 id | user_id | friend_id 

и хотите получить имена общих друзей 2 пользователей. то есть:

 user_relationships 1 | 1 | 3 2 | 2 | 3 users 3| sammy 

у пользователей 1 и 2 есть общий друг 3. Я хочу получить его имя «sammy» в одном запросе.

Как мне это сделать?

 SELECT id, name FROM users WHERE id IN ( SELECT friend_id FROM user_relationships WHERE user_id IN ( 1, 2 ) GROUP BY friend_id HAVING COUNT(friend_id) >= 2 ) 

или с одним соединением:

 SELECT friend_id, name FROM user_relationships r INNER JOIN users u ON r.friend_id = u.id WHERE user_id IN ( 1, 2 ) GROUP BY friend_id HAVING COUNT(friend_id) >= 2 

Вам нужно присоединиться к user_relationships с самим собой, так что две строки с разными user_id имеют один и тот же friend_id

Все общие друзья:

 select ur1.user_id user1, ur2.user_id user2, ur2.friend_id mutual_friend from user_relationships ur1 JOIN user_relationships ur2 ON ur1.friend_id = ur2.friend_id where ur1.user_id != ur2.user_id 

Присоединитесь к таблице пользователей, чтобы получить имена:

 select ur1.user_id user_id1, u1.name User1, ur2.user_id user2, u2.name User2, ur2.friend_id mutual_friend_id, u3.name mutual_friend from user_relationships ur1 JOIN user_relationships ur2 ON ur1.friend_id = ur2.friend_id JOIN user u1 ON u1.user_id = ur1.user_id JOIN user u2 ON u1.user_id = ur2.user_id JOIN user u3 ON ur1.user_id = u3.user_id where ur1.user_id != ur2.user_id 

Вы можете фильтровать общие друзья для определенных пользователей, используя ur1.user_id = first_user и ur2.user_id = second_user

Вы можете попробовать что-то вроде этого:

 select id, name from users where id in (select friend_id from user_relationships where user_id = @user1_id and friend_id in (select friend_id from user_relationships where user_id = @user2_id) ) 

Это должно вернуть всех общих друзей пользователей с идентификаторами @ user1_id и @ user2_id. Это еще не проверено, но должно стать отправной точкой …

не проверял, но этот запрос должен предоставить вам список связанных имен пользователей для ID [no].

 select u1.name, u2.name from users as u1 join user_relationships as ur on u1.id = ur.user_id join users as u2 on ur.friend_id = u2.id where U1.id = [no]; 

Кстати, вам не нужен искусственный идентификатор для вашего кросс-таблицы, так как (user_id, friend_id) уже является законным первичным ключом.