CODEWITHSUNDEEP
sql-servert-sqlunion
Justin
Justin

Reputation: 131

Joining two selects - different column names

I need to build a SELECT statement showing all columns for JV1 - JV6.

How would I go about getting JV2, JV3, etc populated?

SELECT 
Id as [JV1],
vp_pct  as [JV1 Per],
vl_role  as [JV1 Role],
[status]  as [JV1 Status]
FROM era_project_allications_m
WHERE era_project_allications_m.jv_row_id = '1'
UNION ALL
SELECT 
Id as [JV2],
vp_pct  as [JV2 Per],
vl_role  as [JV2 Role],
[status]  as [JV2 Status]
FROM era_project_allications_m
WHERE era_project_allications_m.jv_row_id = '2'

I need the results to display columns containing the JV1, JV2 information.

Thanks for any advice!

Upvotes: 1

Views: 57

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

Join and union are 2 different things. You can't have different column names in union but you can add a column that will hold the source table name (hard coded) to help you understand where the row comes from:

SELECT 
Id,
vp_pct,
vl_role,
[status],
'1' as sourceId
FROM era_project_allications_m
WHERE jv_row_id = '1'

UNION ALL

SELECT 
Id,
vp_pct,
vl_role,
[status],
'2'
FROM era_project_allications_m
WHERE jv_row_id = '2'

though based on your sample code you might just want to do this:

SELECT 
Id,
vp_pct,
vl_role,
[status],
jv_row_id as sourceId
FROM era_project_allications_m
WHERE jv_row_id IN('1', '2'...'n')

Upvotes: 4

Justin
Justin

Reputation: 131

I Figured this one out.

I simply used 6 select statements, giving them each an aliases and a unique ID (project ID as Select_1_ID, Select_2_ID) and LEFT JOIN'd them together using the Select 1's ID).

Rather long-winded but it works - I suspect it isn't very efficient though, Thankfully it only returns 24k rows.

Upvotes: 1

Related Questions