2 引用 MySQL 中相同主键的外键
像这样:
表学生:
sID |名字
1 -----苏珊
2 -----摩根
3 -----伊恩
4 -----詹姆斯
主键 = sID
另一张桌子是这样的:
桌友
朋友ID |人A |人B
friendsID | personA | personB
1------------1------------3
1-----------1-----------3
2------------1------------2
2------------1-----------2
3-------------2------------3
3-------------2-----------3
4-------------3------------4
4-------------3------------4
其中主键是:friendsID、personA、personB
Where Primary Key is: friendsID, personA, personB
外键 = personA, personB 都引用学生表中的sID
Foreign Key = personA, personB which both refer to sID in students table
我想要一种查询朋友表的方法，通过 sID 将 personA 和 personB 列替换为名称.我尝试了自然连接，但它只有在有一个外键时才有效.
I want a way of querying the friends table in such a way that the personA and personB coloumn are replaced by name via sID. I tried natural join but it only works if there is one foreign key.
即我正在寻找这样的东西:
i.e. Im looking for something like this:
朋友ID |人A |人B
friendsID | personA | personB
1------------苏珊------------伊恩
1-----------Susan-----------Ian
2------------苏珊------------摩根
2------------Sushan-----------Morgan
3-------------摩根------------伊恩
3-------------morgan-----------Ian
4-------------伊恩------------詹姆斯
4-------------Ian------------james
如果我只有 personB 作为列而没有 personB，自然连接将起作用.出于某种原因，当我这样做时，自然连接是巨大的:select*from friends NATURAL JOIN student;
the Natural join would work if I only had personB as a column and no personB. For some reason the natural join is huge when I do: select*from friends NATURAL JOIN student;
请帮忙.谢谢
推荐答案
您需要使用两个连接来完成此操作.
You need to use two joins to accomplish this.
例如:
select f.friendsID,
s1.name as personA_name,
s2.name as personB_name
from friends f
inner join student s1 on s1.sID = f.personA
inner join student s2 on s2.sID = f.personB
相关文章