2 引用 MySQL 中相同主键的外键 [英] 2 Foreign Keys referencing the same Primary Key in 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

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