How does the project-version map to jira-issue in database?

[Ma Hao]

Hi,

I want to use SQL query to generate a report which including issue fix'version.

But I did not find how the project version mapped to issue fix version field.

I can not find how the "fix version" field stored in database, it is not existed in "jiraissue" table.

Does any one know about the fix version from database scheme?

JIRA version: 6.3.11

Thanks in advance.

[Leedrick · Accepted solution]

What @Volodymyr Krupach told you won't help. That will end up giving you all the issues linked to a project x the project versions.

Here is the info I think you need:

SELECT projectversion.id, vname
FROM projectversion,
nodeassociation,
jiraissue
WHERE ASSOCIATION_TYPE = 'IssueFixVersion'
AND SINK_NODE_ID = projectversion.id
AND SOURCE_NODE_ID = jiraissue.id
AND pkey = '<issue_key>';

https://confluence.atlassian.com/display/JIRA041/Example+SQL+queries+for+JIRA

You need to use the nodeassociation join table where the type is 'IssueFixVersion' to connect an issue to a fixversion.

[Ma Hao]

Thanks a lot. I will try this sql.

[Volodymyr Krupach]

Hi Ma!

There is projectversion table that has foreign key to project. jiraissue table has the foreign key to project as well.

image2015-4-11 11:56:55.png

Please see here for more details: https://developer.atlassian.com/jiradev/jira-architecture/database-schema