MySQL- How to get user information and also their groups

Hey,

I would need a MySql query where I get all my users (name,username,email,groups) from the JIRA Internal Directory.

 

With this query I get all the user information except the groups. But I would need the groups too.

select * from cwd_user where directory_id = 1
THX smile

 

 

 

1 answer

1 accepted

0 vote

Join cwd_user on to cwd_membership

@Nic Brough [Adaptavist]Thx for your fast answer. I tried following query:

 

SELECT *

FROM cwd_user AS cu1

JOIN cwd_user AS cu2

  ON cwd_membership  

WHERE directory_id = 1;

 

Now I get the error "Column 'directory_id' in where clause is ambiguous".

Ah, sorry, I should have said - it needs to be joined on lower_child_name = username

I still get the same error if I run this query:

SELECT *

FROM cwd_user AS cu1

JOIN cwd_user AS cu2

  ON cwd_membership

  AND lower_child_name = username   

WHERE directory_id = 1;

 

Did I forget something in my query?

Simplify the SQL and make it explicit.  (The "as" phrase is the bane of SQL readability.  It's great when you're an expert DBA and no-one else has to understand what you're doing, but for most of us, it really just makes it all harder to read)

The most simple and precise form of this query is

Select * from cwd_user join cwd_membership on cwd_membership.lower_child_name = cwd_user.username

To limit the directory, add

where cwd_user.directory_id = 1

Hey Nic Brough,

I'm sorry for it and no I'm not an expert DBA.

thx again for your help but now I get another error.

"ERROR 1054 (42S22): Unknown column 'cwd_user.username' in 'on clause'"

Do you have any idea whats the problem here?

Yes, the problem is that I was looking at the wrong system.

Please replace it with cwd_user.lower_user_name

Sorry!

Ahh that's great, now it works. Lovely! Many Thx!

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,319 views 14 20
Join discussion

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot