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