database - relation between project and issue type

[Malesys Elie]

Hi,

I'm trying to get the list of project using "Story" as Issue type.

So, on my test project, I tried to list issue types with the solution proposed on this question : https://community.atlassian.com/t5/Jira-questions/database-issue-type-of-a-project/qaq-p/151844 :

SELECT IT.id, IT.pname
FROM configurationcontext CC
LEFT OUTER JOIN optionconfiguration OC ON OC.fieldconfig = CC.fieldconfigscheme
LEFT OUTER JOIN issuetype IT ON IT.id = OC.optionid
WHERE CC.project = 116830 and CC.customfield = 'issuetype'

but it gives wrong result

so I have done the following test :

-creating a new issue type scheme (containing only "Story)

- check the last update on optionconfiguration in order to get the new scheme entry (select * from optionconfiguration order by id desc) => result : fieldconfig = 120292

- associate this new scheme to test project

-check the fieldconfigscheme value (select fieldconfigscheme from configurationcontext where project = 116830) => result : fieldconfigscheme = 102392

so the problem is that OC.fieldconfig and CC.fieldconfigscheme doesn't match so my question is : How works the relationship between issue type scheme and issue types?

I'm using Jira 7.7.1

Regards,

Elie.

[Malesys Elie]

I found the solution, the relation between OC.fieldconfig and CC.fieldconfigscheme is in fieldconfigschemeissuetype table (lets call it R) with CC.fieldconfigscheme = R.fieldconfigscheme and R.fieldconfiguration = OC.fieldconfig)

Regards,

Elie.