SQL to dump Components by Project?

[Robert Leachman]

We run Data Center and I can execute SQL. We need to provide a list of all our configured Project/Component combinations.

The closest thing I found online works all the issues and reports the count of issues by Component, which is close I suppose but comes short of the goal.

Can someone help with this query please? Thanks!

[Peter-Dave Sheehan · Accepted solution]

Here is a simple SQL that will list all the projects and their components with some of the component details:

Select
   p.pname,
   p.pkey,
   c.cname,
   c.description,
   c.LEAD,
   c.ASSIGNEETYPE,
   c.ARCHIVED,
   c.DELETED
From
   project p Inner Join
   component c On p.ID = c.PROJECT

 You can decide whether or not to include archived or deleted components.

[Robert Leachman]

Perfect :) Thank you very much PDS!

[Trudy Claspill · Accepted solution]

Hello @Robert Leachman 

What exactly is your goal?

This will get you a list of all the components and the project with which each one is associated, with all the information for each component and project record.

SELECT * FROM component as "co"
left join project "pr" on co.project = pr.id

[Robert Leachman]

Thank you Trudy!

My company drives issue triage from the Project/Component combination and we want to audit our Jira configuration and make sure it matches up properly to the business teams. Lots of meta beyond listing the Project Lead.

Your query is great!