Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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
TAGS

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you