Jira MySQL query that returns project names, that...

[Igor Pryadunenko]

Hi every one. Need help with SQL query that will return me names of all projects, in witch "SOME GRUP" is not member of "SOME PROJECT ROLE"

Somthing like:

SELECT * FROM projectname WHERE jira-group(or user) NOT IN project-role;

[Igor Pryadunenko · Accepted solution]

Right answer is:

select projects.pname from jira.project as projects 

inner join jira.projectroleactor as pa on pa.PID = projects.ID 

inner join jira.projectrole as pr on pr.ID = pa.PROJECTROLEID

where pa.ROLETYPEPARAMETER = "<here my group name>" and pa.PROJECTROLEID != 10800

 1080 i got from jira.projectrole and its ID of my needed project role.

[Alexey Matveev]

That would the SQL Query

select * from jira.project a where not exists
(SELECT pid FROM jira.projectroleactor b where
roletypeparameter = 'USER OR GROUP' and a.id = b.pid)

[Igor Pryadunenko]

Thank you, so much. You answer is not absolutely applicable in my case, but it gave me right direction. 

[Domenico Manzo _Actonic_]

Is it mandatory requirement to use SQL only? Could you use REST, for example?

[Igor Pryadunenko]

In my case it`s the only way to get needed information.