SQL Query to list projects having a specific issue type

[Parashar Joshi]

Hi All,

Greetings!

I have been looking to find an SQL that can provide the list of all projects which list all the projects using a specific issue type.

I found this thread List of project using a particular issue type.

However, the issue is that it does not list the projects that use the "Default Issue type Scheme" as i see no entries for them in the "OPTIONCONFIGURATION" table.

Can anyone help on this issue?

Thanks,
P

[korney yakovenko]

Hi, Joshi

This query will return all projects that use the specified issue type.

select IT.id issue_id, IT.pname issuetype, PROJ.pname project_name,CC.customfield
from configurationcontext CC
join optionconfiguration OC on OC.fieldconfig = CC.fieldconfigscheme
join issuetype IT on IT.id = OC.optionid
join project PROJ on PROJ.id = CC.project
where lower(IT.pname) like '%epic%'  --specify the issue type you want
order by IT.pname;

[Parashar Joshi]

Thank you very much for the answer and first response.

What i noticed was that this query returns fewer projects than the other query.
We are expecting about 800+ projects where my query returns 500+ projects and your's returns about 300.

So something is missed and yet to look at DB design for it.
we are also working with support and searching atlassian KB to get any info.

[Parashar Joshi]

My original query is

SELECT DISTINCT P.PKEY as proj_key
FROM JIRA.PROJECT P JOIN JIRA.CONFIGURATIONCONTEXT CC ON P.ID = CC.PROJECT
JOIN JIRA.FIELDCONFIGSCHEME FCS ON FCS.ID = CC.FIELDCONFIGSCHEME
JOIN JIRA.FIELDCONFIGSCHEMEISSUETYPE FCI ON FCI.FIELDCONFIGSCHEME = CC.FIELDCONFIGSCHEME
JOIN JIRA.OPTIONCONFIGURATION OC ON OC.FIELDCONFIG = FCI.FIELDCONFIGURATION
JOIN JIRA.ISSUETYPE IT ON IT.ID = OC.OPTIONID
where (IT.PNAME = 'XYZ' or IT.PNAME = 'ABC')
order by proj_key;

[korney yakovenko]

Yes, inded. I can offer you a request that will return all projects of the specified type, but only under the condition that there is at least one task according to your type.

select distinct ji.project project_id,

pr.pname project_name,

it.pname issue_type 

from jiraissue ji
join issuetype it on it.id=ji.issuetype
join project pr on pr.id=ji.project
where it.pname='Epic';

[Parashar Joshi]

Thank you again.

But we have to list all projects even when there are no issues for that issue type for a given project.

-P