MySQL- How to get user information and also their groups

[Bernhard Riegler]

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

[Nic Brough -Adaptavist- · Accepted solution]

Join cwd_user on to cwd_membership

[Bernhard Riegler]

@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".

[Nic Brough -Adaptavist-]

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

[Bernhard Riegler]

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?

[Nic Brough -Adaptavist-]

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

[Bernhard Riegler]

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?

[Nic Brough -Adaptavist-]

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

Please replace it with cwd_user.lower_user_name

Sorry!

[Bernhard Riegler]

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