如何比较两列的重复值?

I have a table with the id of several users and their respective friends ID, I want to see who has a mutual friend.

我有一个有几个用户id和他们的朋友id的表格，我想看看谁有一个共同的朋友。

Example, 1 is the ID of Roger and 2 is the id of Matt.

例如，1是Roger的ID, 2是Matt的ID。

TABLE FRIENDS FIELDS/VALUES:

表的朋友字段/值:

ID, FRIEND_ID, FRIEND_NAME

1, 34, DAMON

1, 17, RICHARD

1, 56, DANIEL

1, 65, CHARLIE

2, 15, PRISCILA

2, 17, RICHARD

2, 45, JOHN

2, 56, DANIEL

I want to create a select with ID 1 and 2, that will return the rows that have the FRIEND_ID in common(meaning which friend Roger and Matt have in common), in this case, it would return the cols with the friend_id RICHARD and DANIEL, preferably, not duplicated.

我想创建一个ID为1和2的select，它将返回具有共同的FRIEND_ID的行(意思是Roger和Matt有共同的朋友)，在这种情况下，它将返回带有FRIEND_ID RICHARD和DANIEL的cols，最好不要重复。

2 个解决方案

#1

This should work,

这应该工作,

Select f1.FRIEND_ID,f1.FRIEND_NAME from 
FRIENDS f1,FRIENDS f2 where f1.FRIEND_ID =f2.FRIEND_ID and 
f1.id=1 and f2.id=2

here is the sample: http://sqlfiddle.com/#!2/c9f36/1/0

以下是示例:http://sqlfiddle.com/#!2/c9f36/1/0。

also if you want to get all people having common friends try this

如果你想让所有人都有共同的朋友，试试这个

Select f1.FRIEND_ID,f1.FRIEND_NAME,f1.id 'first person',f2.id as 'second person' from 
FRIENDS f1,FRIENDS f2 where f1.FRIEND_ID =f2.FRIEND_ID and 
f1.id<>f2.id and f1.id<f2.id

this will return two people having same friends per row: http://sqlfiddle.com/#!2/c9f36/2/0

这将返回每行有相同朋友的两个人:http://sqlfiddle.com/#!2/c9f36/2/0

#2

SELECT friend_id, friend_name
FROM friends
WHERE id IN (1, 2)
GROUP BY friend_id
HAVING COUNT(*) > 1

DEMO.

演示。

#1