I have two tables, one with this form:
ID, IDPupil1, IDPupil2 1 3000 3001
and the second this form:
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
You need to joins to the
pupils table twice: Once for
IDPupil1 and once for
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