SQL SELECT JOIN COLUMN ALIAS

Question

Thanks in advance for your help:

[People] table has two columns:

• PersonID
• PersonName

[Marriages] table has three columns:

• MarriageID
• PersonIDa
• PersonIDb

I want to SELECT the following columns:

• MarriageID
• PersonName (for personIDa)
• PersonName (for personIDb)

I think I need to use a SELECT statement with a JOIN, but I'm not sure how to assign a unique alias to each PersonName. This is what I have so far (that doesn't work):

SELECT 
    [Marriages].[MarriageID], 
    [People].[PersonName] AS aName, 
    [People].[PersonName] AS bName  
FROM 
    [Marriages]  
JOIN 
    [People] ON [Marriages].[PersonIDa] = [People].[PersonID]  
JOIN 
    [People] ON [Marriages].[PersonIDb] = [People].[PersonID]  

Thanks again...

Answer 1

Does this match what you're trying to accomplish?

SELECT m.[MarriageID], peopleA.[PersonName] AS aName, peopleB.[PersonName] AS bName  
FROM [Marriages]  M
JOIN [People] peopleA ON m.[PersonIDa] = peopleA.[PersonID]  
JOIN [People] peopleB ON m.[PersonIDb] = peopleB.[PersonID]  

Answer 2

I think you're trying to do this...

SELECT 
m.[MarriageID], 
pa.[PersonName] AS aName,
pb.[PersonName] AS bName  
FROM [Marriages] m
JOIN [People] pa ON m.[PersonIDa] = pa.[PersonID]  
JOIN [People] pb ON m.[PersonIDb] = pb.[PersonID];

Answer 3

You will need something like:

Select MarriageID, a.PersonName, b.PersonName
From Marriages m
Join People a On a.PersonID = m.Person1ID 
Join People b on b.PersonID = m.Person2ID