MySQL - 如何比较两个表之间的共同值

I have two tables.

我有两张桌子。

TABLE FRIENDS - Columns/Values

表格友好 - 列/值

ID, FRIEND_ID, FRIEND_NAME

ID,FRIEND_ID,FRIEND_NAME

1, 34, DAMON

1,34,DAMON

1, 17, RICHARD

1,17,理查德

1, 56, DANIEL

1,56,丹尼尔

1, 65, CHARLIE

1,65,查理

2, 15, PRISCILA

2,15,PRISCILA

2, 17, RICHARD

2,17,理查德

2, 45, JOHN

2,45,约翰

2, 56, DANIEL

2,56,丹尼尔

TABLE USERS - Columns/Values

表用户 - 列/值

USER_ID, NAME

1, JUNIOR

2, SAMANTHA

15, PRISCILA

45, JOHN

56, DANIEL

How do I do a SELECT that query all the rows from table FRIENDS where friends.USER_ID = X and cross reference with all the rows in table USERS where the friends.FRIEND_ID = users.USER_ID.

我如何执行查询表FRIENDS中所有行的SELECT,其中friends.USER_ID = X并且交叉引用表USERS中所有的行,其中的friends.FRIEND_ID = users.USER_ID。

Basically I want to query all the friends of a user(from table FRIENDS, identified by friend_id) that also exist in the table USERS(if they exist in the table friends.FRIEND_ID = users.USER_ID).

基本上我想查询表USERS中存在的用户的所有朋友(来自表FRIENDS,由friend_id标识)(如果它们存在于表friends.FRIEND_ID = users.USER_ID中)。

Sorry for the bad english, it's not my native language.

对不起英语不好,这不是我的母语。

2 个解决方案

#1

This "cross" of data is aptly named a "JOIN". There are different flavors of JOINs, but for you, a simple "inner" join should suffice:

这种“交叉”的数据恰如其分地命名为“JOIN”。 JOIN有不同的风格,但对你来说,一个简单的“内部”连接就足够了:

SELECT
    f.*
FROM
    FRIENDS f
JOIN
    USERS u
    ON
    r.FRIEND_ID = u.USER_ID
WHERE
    f.FRIEND_ID = ...

#2

First solution : using JOIN

第一个解决方案:使用JOIN

SELECT f.* 
FROM friends f JOIN users u ON u.USER_ID = f.FRIEND_ID 
                           AND u.USER_ID = '?'

Second solution : using EXISTS

第二种解决方案:使用EXISTS

SELECT f.* 
FROM friends f 
WHERE EXISTS (SELECT * 
              FROM users 
              WHERE USER_ID = f.FRIEND_ID 
                AND USER_ID = '?')

#1