1

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

1 Answer 1

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.