CODEWITHSUNDEEP
sqlsql-serversql-server-2008
JustJeffy
JustJeffy

Reputation: 97

How to get linked table value from two different fields in the same row into a single row result set

I have a "match" table that shows opponents for a game. There is a HomeTeamID and an AwayTeamId field in a single row. The ID fields relate to a "Person" table that has Name information. What I need is a query to create a single row with the match that contains the person name instead of the id. I assume it is some form of union and grouping, but for the life of me I can't figure it out.

MATCH TABLE :

ID,  DATE,      TIME,    HOMEID, AWAYID
105, 12/1/2018, 5:00 PM, 46,     53

PERSON TABLE :

ID, NAME
46, John Doe
53, Mike Smith

Output needed:

105, 12/1/2018, 5:00 PM, 46, John Doe, 53, Mike Smith

Upvotes: 0

Views: 31

Answers (1)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112392

Since you need to join the person table twice, you must give it two different aliases

SELECT m.ID, m.DATE, m.TIME, m.HOMEID, home.NAME, m.AWAYID, away.NAME
FROM
    MATCH m
    INNER JOIN PERSON home
        ON m.HOMEID = home.ID
    INNER JOIN PERSON away
        ON m.AWAYID = away.ID

Upvotes: 2

Related Questions