How can you use SQL to find the component id for an issue in the oracle tables?

Amber McCallister February 10, 2015

I see a table called component which gives me the component IDs for a project (19130). In the JIRAISSUE table there is a component column but they are all null. I need to find the link between the issue and the component table.

5 answers

1 accepted

6 votes
Answer accepted
Andrey Kuzmin
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 10, 2015

It's going to be something like this:

SELECT ji.issuenum, na.sink_node_id component_id
FROM   jiraissue ji
JOIN   nodeassociation na
ON     na.source_node_id = ji.id
       AND na.sink_node_entity = 'Component'
       AND na.source_node_entity = 'Issue'
WHERE  ji.project = 19130
Amihai Markovitz June 17, 2020

Hi,

I am using this tip but I still don't get the Component value, any other idea?

1 vote
Bhagya M S February 6, 2018

You can use the below query to fetch the components for a particular issue in JIRA

Please specify the valid jira issue key for below query:

note: in my case jira issue key is 'TEST-171'

SELECT
(case when (LENGTH(CM.CNAME)>0) then CM.CNAME else ' ' end) AS COMPONENT
FROM JIRAISSUE JI, Project P, COMPONENT CM, NODEASSOCIATION NA  where CM.PROJECT=P.ID AND JI.PROJECT = P.ID AND NA.SOURCE_NODE_ID=JI.ID AND NA.SINK_NODE_ID=CM.ID AND P.PKEY||'-'||JI.ISSUENUM= 'TEST-171'

1 vote
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 10, 2015

The component column is a hangover from older ways of doing 

Have a look in "nodeassociation" - I can't remember the name of the lines you need, but one column will have the JIRA issue id, and another the component id, with the third column containing something like "componentlink"

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 11, 2015

Give Andrey the points :-)

0 votes
Gregory Riccardi March 18, 2020

Hey Guys this is a big help, I am looking for something a little more simple however.

I would like a list of all component names in a project. 

Any ideas?

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 25, 2020

Search the component table where project = the id of the project.

Like ban yige likes this
ban yige July 22, 2021

Excuse me, does this project still exist ? I cannot find it. 

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 22, 2021

What project?

ban yige July 22, 2021

2021-07-22_06-42-13.png

 the id of the project

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 22, 2021

I'm afraid that does not explain your question.  What project are you asking about?

Actually, what are you trying to do here?  That might be easier to tell us than explain what "project that might not exist" is

0 votes
Amber McCallister February 11, 2015

Nic and Andrey,

I wanted to thank you both for all of your help with this.

Thanks,

Amber

Suggest an answer

Log in or Sign up to answer