What is the correct entity relationship to query JIRA user groups?

[Sameera Shaakunthala [inactive]]

What is the valid SQL among the following two?

Shall I join by IDs or parent/child names? These two SQLs yield different row counts.

select *
from
  cwd_user u,
  cwd_group g,
  cwd_membership m
where
  m.child_id = u.id and
  m.parent_id = g.id;
  
select *
from
  cwd_user u,
  cwd_group g,
  cwd_membership m
where
  m.lower_child_name = u.lower_user_name and
  m.lower_parent_name = g.lower_group_name;

[Jobin Kuruvilla [Adaptavist]]

Use child_id and parent_id. Note that if you have nested groups enabled, there may GROUP_GROUP relation in the membership table as well!

[Yilin]

May you can tell me what exactly you want to count? the number of user counted in the license maybe?

[Sameera Shaakunthala [inactive]]

This is for auditing purposes. Follwing are my required outputs:

• Users counted in the license & having access to JIRA.
• Users having bulk change permission.
• Internal users (for those who have been granted the permission to browse users)

My requirement is a list of users that can be exported to a spreadsheet program.