共同好友SQL with Join(Mysql) [英] Mutual friends sql with join (Mysql)

问题描述

我有两个桌子

用户表:

id|name

user_relationships

user_relationships

id | user_id | friend_id

，并希望获得2个用户的共同朋友的名字.即:

and want to get names of mutual friends of 2 users. i.e:

user_relationships
1 | 1 | 3
2 | 2 | 3  

users
3| sammy

用户1和2有共同的朋友3.我想在一个查询中得到他的名字"sammy".

users 1 and 2 have mutual friend 3. I want to get his name 'sammy' in one query.

我该怎么做?

推荐答案

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

这篇关于共同好友SQL with Join(Mysql)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！