2 Foreign Keys referencing the same Primary Key in

问题:

like so:

Table-student:

sID | name

1 -----susan

2 -----morgan

3 -----Ian

4 -----james

Primary key = sID

the other table like so:

Table- friends

friendsID | personA | personB

1-----------1-----------3

2------------1-----------2

3-------------2-----------3

4-------------3------------4

Where Primary Key is: friendsID, personA, personB

Foreign Key = personA, personB which both refer to sID in students table

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 | personA | personB

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

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

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

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

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;

Please help. Thanks

回答1:

You need to use two joins to accomplish this.

For example:

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