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

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.

4 answers

1 accepted

This widget could not be displayed.

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
This widget could not be displayed.

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"

Give Andrey the points :-)

This widget could not be displayed.

Nic and Andrey,

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

Thanks,

Amber

This widget could not be displayed.

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'

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted yesterday in Jira

What modern development practices are at the heart of how your team delivers software?

Hey Community mates! Claire here from the Software Product Marketing team. We all know software development changes rapidly, and it's often tough to keep up. But from our research, we've found the h...

69 views 0 1
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you