2个外键在MySQL中引用相同的主键 [英] 2 Foreign Keys referencing the same Primary Key in MySQL

问题描述

像这样:

Table-student:

Table-student:

sID |名称

1 -----苏珊

2 -----摩根

3 -----伊恩

4个----- james

4 -----james

主键= sID

另一个表是这样的:

餐桌朋友

friendsID |人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

我想要一种查询friends表的方式，其中personA和personB列通过sID被名称替换.我尝试了自然联接，但是只有在有一个外键的情况下它才起作用.

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:

friendsID |人A |人B

friendsID | personA | personB

1 -----------苏珊-----------伊恩

1-----------Susan-----------Ian

2 ------------苏珊-----------摩根

2------------Sushan-----------Morgan

3 -------------摩根-----------伊恩

3-------------morgan-----------Ian

4 -------------伊恩------------詹姆斯

4-------------Ian------------james

如果我只有personB作为列，而没有personB，则自然联接将起作用.出于某种原因，当我这样做时，自然连接非常巨大: 从朋友NATURAL JOIN学生中选择*；

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

这篇关于2个外键在MySQL中引用相同的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！