Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,368,969
Community Members
 
Community Events
168
Community Groups

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.

5 answers

1 accepted

6 votes
Answer accepted

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

Hi,

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

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

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 :-)

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?

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

Like ban yige likes this

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

What project?

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

 the id of the project

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

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