如何從兩個索引中選擇屬性，都指向同一個表？

我有兩張桌子，一張是這種表格：

桌隊：

ID, IDPupil1, IDPupil2
1   3000      3001

第二個這種形式：

表學生：

ID,   Name,   Forename
3000  Smith   John
3001  Morgan  Lisa

IDPupil1 和 IDPupil2 引用學生 ID。

現在我想在一個 SQL 查詢中選擇所有這些值。我試過這個：

SELECT teams.ID AS ID, IDPupil1, IDPupil2, pupils.Name AS Name, pupils.Forename AS Forename
FROM teams
LEFT JOIN pupils
ON IDPupil1 = pupils.ID
AND IDPupils2 = pupils.ID

結果是這樣的：

ID,  IDPupil1, IDPupil2, Name, Forename
1    3000      3001      NULL  NULL

當我取消查詢 ( AND IDPupil2 = pupils.ID) 的最後一行時，會顯示 Pupil1 的姓名和名字。但是對於這兩個索引，它顯然不起作用。

那麼我怎樣才能得到以下結果呢？

ID,  IDPupil1, IDPupil2, Name1, Forename1, Name2, Forename2
1    3000      3001      Smith  John       Morgan Lisa

您需要加入pupils表兩次：一次 forIDPupil1和一次 for IDPupil2：

SELECT 
   t.ID AS ID, t.IDPupil1, t.IDPupil2, pupils1.Name AS Name1,
   pupils1.Forename AS Forename1, pupils2.Name AS Name2, 
   pupils2.Forename2 AS Forename
FROM teams t
LEFT JOIN pupils pupils1
ON t.IDPupil1 = pupils1.ID
LEFT JOIN pupils pupils2
AND t.IDPupils2 = pupils2.ID

引用自：https://dba.stackexchange.com/questions/209195