Merging two tables with SQL to transform ID into Name

Megan July 21, 2023

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'

2 answers

1 accepted

3 votes
Answer accepted
Katerina Rudkovskaya [Stiltsoft]
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
July 24, 2023

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:

Mon 8-1.png

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'


Mon 8-2.png

Hope it helps your case.

Megan July 24, 2023

This worked perfectly, thank you so much!

Like Stiltsoft support likes this
0 votes
Rodney Paul
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
July 21, 2023

Clickbankaffliates.com

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events