How to select attribute from two indexes, both pointing at the same table?

Posted on

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:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *