Mysql在引用相同密钥的同一个表中加入2个外键

I have the 2 tables below.

我有两张桌子。

Table: Users

表：用户

user_id     username
--          --
1           name1
2           name2
3           name3

Table: Rel

表：Rel

user1       user2
--          --
1           2
3           2
1           3

My goal is to be able to retrieve it like this :

我的目标是能够像这样检索它：

user1       user2
--          --
name1       name2
name3       name2
name1       name3

Excuse my bad terminology and English. The user1 and user2 are foreign keys from users.user_id and together makes composite key.

请原谅我糟糕的术语和英语。 user1和user2是来自users.user_id的外键，并且一起创建组合键。

I am able to get one column like so below

我可以得到如下所示的一列

SELECT users.username
FROM users
JOIN rel ON rel.user1 = users.user_id

But when I try to get them together like displayed above with goal I don't manage to get it working at all. If anybody have any suggestions I would be really grateful

但是当我尝试将它们组合在一起时，如上面显示的目标，我根本无法让它工作。如果有人有任何建议我会非常感激

2 个解决方案

#1

You should really "just try something" before asking. Try this:

在询问之前你应该“尝试一下”。尝试这个：

SELECT u1.username user1, u2.username user2
FROM rel
JOIN users u1 ON rel.user1 = u1.user_id
JOIN users u2 ON rel.user2 = u2.user_id

A most important part to note is that you MUST use "table aliases" to differentiate between the first join and the second join to the same table. "u1" and "u2" are the aliases I chose for this example.

需要注意的一个最重要的部分是你必须使用“表别名”来区分同一个表的第一个连接和第二个连接。 “u1”和“u2”是我为此示例选择的别名。

#2

So if your schema is:

因此，如果您的架构是：

CREATE TABLE Users (user_id int, username varchar(50));
INSERT INTO Users (user_id, username) VALUES (1, 'name1');
INSERT INTO Users (user_id, username) VALUES (2, 'name2');
INSERT INTO Users (user_id, username) VALUES (3, 'name3');

CREATE TABLE Rel (user1 int, user2 int);
INSERT INTO Rel (user1, user2) VALUES (1, 2);
INSERT INTO Rel (user1, user2) VALUES (3, 2);
INSERT INTO Rel (user1, user2) VALUES (1, 3);

You can use the following query:

您可以使用以下查询：

SELECT u1.username as user1, u2.username as user2
FROM Rel r
JOIN Users u1 ON r.user1 = u1.user_id
JOIN Users u2 ON r.user2 = u2.user_id

- >

+---------+---------+
| user1   | user2   |
|---------+---------|
| name1   | name2   |
| name3   | name2   |
| name1   | name3   |
+---------+---------+
3 rows in set
Time: 0.002s

#1