Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Jira SQL for Nested Groups

Edited

I am trying to create a report that looks like following (by avoiding duplicates):

Parent_Group | child_group | member

The only relevant table I have found so far is cwd_membership. I found that one child_group belongs to multiple parent groups. Below is one example:

 jira_groups.PNG

If I use the below SQL, I get the correct list of members who belong to child group. I haven't been able to find a way to get the details about what parent group they belong to. For example, if user "John" is a part of Project Management group, how will I know if John is the member of Project Management subgroup that belongs to one of the above parent groups? 

Select usr.parent_id, usr.child_id, grp.parent_name AS parent_group, usr.parent_name AS child_group, usr.child_name AS user_name
from
(Select parent_id, child_id, membership_type, parent_name, child_name
from public.cwd_membership WHERE membership_type = 'GROUP_GROUP') AS grp
JOIN
(Select parent_id, child_id, membership_type, parent_name, child_name
from public.cwd_membership WHERE membership_type = 'GROUP_USER') AS usr
ON usr.parent_id = grp.child_id

 

0 answers

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you