join with mysql,共同好友SQL with Join（Mysql）

I have two tables

users table:

id|name

user_relationships

id | user_id | friend_id

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

user_relationships

1 | 1 | 3

2 | 2 | 3

users

3| sammy

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

How do I do that?

解决方案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

)

or with one join:

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