Question :
I have two tables, one with this form:
table teams:
ID, IDPupil1, IDPupil2
1 3000 3001
and the second this form:
table pupils:
ID, Name, Forename
3000 Smith John
3001 Morgan Lisa
IDPupil1 and IDPupil2 are referencing at pupils.ID.
Now I want to select all this values in one SQL-query.
I tried this:
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
The result is this:
ID, IDPupil1, IDPupil2, Name, Forename
1 3000 3001 NULL NULL
When I cancel the last row of the query (AND IDPupil2 = pupils.ID
), then the Name and Forename of Pupil1 is shown. But with both indexes it obviously doesn’t work.
So how can I get the following result?
ID, IDPupil1, IDPupil2, Name1, Forename1, Name2, Forename2
1 3000 3001 Smith John Morgan Lisa
Answer :
You need to joins to the pupils
table twice: Once for IDPupil1
and once 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