CODEWITHSUNDEEP
sqlsql-server
user1950212
user1950212

Reputation: 61

SQL SELECT JOIN COLUMN ALIAS

Thanks in advance for your help:

[People] table has two columns:

[Marriages] table has three columns:

I want to SELECT the following columns:

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...

Upvotes: 6

Views: 20316

Answers (5)

user1950212
user1950212

Reputation: 61

Thanks everyone; your suggestions worked perfectly. I was trying to create an alias for the repeated column in the SELECT statement when I should have been trying to create an alias for the repeated table in the JOIN statement.

For anyone else who runs into this: 

SELECT  
[TABLE1 NAME].[TABLE1 COLUMN],  
(TABLE2 ALIAS1).[TABLE2 COLUMN],  
(TABLE2 ALIAS2).[TABLE2 COLUMN],  
FROM [TABLE1 NAME]  
JOIN [TABLE2 NAME] (TABLE2 ALIAS1) ON [TABLE1 NAME].[TABLE1 MATCHING COLUMN] = (TABLE2 ALIAS1).[TABLE2 MATCHING COLUMN]  
JOIN [TABLE2 NAME] (TABLE2 ALIAS2) ON [TABLE1 NAME].[TABLE1 MATCHING COLUMN] = (TABLE2 ALIAS2).[TABLE2 MATCHING COLUMN]

The alias name is created in the JOIN statement and applied to where it is used in the SELECT statement, not the other way around.

Upvotes: -1

gifts hyderabad
gifts hyderabad

Reputation: 19

Don't forget what type of JOIN you need..."INNER JOIN" / "LEFT OUTER JOIN" / "RIGHT OUTER JOIN"/ "CROSS JOIN"

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

Upvotes: 1

Sebastian K
Sebastian K

Reputation: 6373

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

Upvotes: 1

Maurice Reeves
Maurice Reeves

Reputation: 1583

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]

Upvotes: 8

mservidio
mservidio

Reputation: 13057

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];

Upvotes: 2

Related Questions