Find the projects in a project catergory.

[Shreyance Shaw]

Can someone let me know in which table do i find the projects associated with a project category so that i can form a SQL Query.

Also if its possible to do the same via the soap service i would be much happier

[Shreyance Shaw · Accepted solution]

select cname Category, project.id as ProjectID, pname ProjectName, pkey ProjectKey, vname VersionName, projectversion.id VersionID
from jirauser.project, jirauser.nodeassociation, jirauser.projectcategory, jirauser.projectversion
where association_type = 'ProjectCategory'
and sink_node_id = projectcategory.id
and project.id = source_node_id
and project.id = projectversion.project

[Shreyance Shaw]

select cname Category, project.id as ProjectID, pname ProjectName, pkey ProjectKey, vname VersionName, projectversion.id VersionID
from jirauser.project, jirauser.nodeassociation, jirauser.projectcategory, jirauser.projectversion
where association_type = 'ProjectCategory'
and sink_node_id = projectcategory.id
and project.id = source_node_id
and project.id = projectversion.project

[Zul NS _Atlassian_]

You can check the project table and the projectcategory table, connected by the project ID. Furthermore, the following might help

https://developer.atlassian.com/display/JIRADEV/Database+Schema