Merging two tables with SQL to transform ID into Name

Megan_dbib63

I am utilizing a jira plugin to display a list of tickets and their associated owners. I have four name fields - Operations Owner, Partner Owner, Product Owner, Engineering Owner - that come through Jira as IDs. I want to match the IDs to a name and merge the two tables together so only the persons human readable name displays. 

 

So far, I can only figure out how to get two names to display based on SQL. Any help would be appreciated

 

 

Table 1

Summary | Operations Owner | Partner Owner | Product Owner | Engineering Owner 

ABC | 123 | 111 | 112 | 145 

 

Table 2

ID | Name

123 | John Smith

145 | John Doe 

111 | Person C 

112 | Person D

 

The Join I have so far - below displays this 

ABC | 123 | Person C | Person D | 145

 

-----

 

SELECT 'Summary',
'Partner Owner',
'Name' AS 'Product Owner'
FROM
(SELECT 'Summary',
'Name' AS 'Partner Owner',
'Product Owner'
FROM T1 LEFT JOIN T2 ON T1.'Partner Owner' = T2.'ID')
LEFT JOIN T2 ON 'Product Owner' = T2.'ID'

Katerina Rudkovskaya _Stiltsoft_

Hi @Megan,

You are on the right way, just expand the existing structure of your SQL query to cover all the columns where you need to replace IDs by names:



SELECT 'Col 1', 'Col 2', 'Col 3','Col 4',
'Name' AS 'Col 5'
FROM
(SELECT 'Col 1', 'Col 2', 'Col 3',
'Name' AS 'Col 4',
'Col 5'
FROM
(SELECT 'Col 1', 'Col 2',
'Name' AS 'Col 3',
'Col 4', 'Col 5'
FROM
(SELECT 'Col 1',
'Name' AS 'Col 2',
'Col 3', 'Col 4', 'Col 5'
FROM T1
LEFT JOIN T2 ON T1.'Col 2' = T2.'ID')
LEFT JOIN T2 ON 'Col 3' = T2.'ID')
LEFT JOIN T2 ON 'Col 4' = T2.'ID')
LEFT JOIN T2 ON 'Col 5' = T2.'ID'

Hope it helps your case.