How do I find a set of users that are not part of one group and are active

[Mike]

I am working to find users who have been left over active but have no active groups.

Although this sql query is helpful, it does not determine if they are still active or not:

SELECT * 
FROM cwd_user
WHERE id NOT IN (SELECT m.child_user_id FROM cwd_membership AS m
		 JOIN cwd_group AS g ON m.parent_id = g.id
		 WHERE g.group_name = '<group_name>');

Having issues adding or similar:

where ACTIVE = 'T'; 

Any help would be greatly appreciated. Thanks 

Confluence 6.6.6

[Shawn C · Accepted solution]

Hi Mike, 

For the most part, your SQL looks right however you'd want to use a lowercased "active" in your SQL if you want to filter by it.  Something like this should work:

SELECT * 
FROM cwd_user
WHERE id NOT IN (SELECT m.child_user_id FROM cwd_membership AS m
 JOIN cwd_group AS g ON m.parent_id = g.id
 WHERE g.group_name = '<group_name>')
 and active = 'T' ;

What's happening when you try to include your where active condition?

Cheers,
Shawn

[Mike]

Thanks Shawn. I will see if I can work with this query output. My goal was to find users who are not associated to any groups and are active

[Shawn C]

Hi Mike, 

I get you now; try this one and it should give you a list of Active Users that do not have group memberships: 

SELECT U.ID, U.user_name, G.group_name
FROM cwd_user U
LEFT JOIN cwd_membership M ON U.ID = M.child_user_id
LEFT JOIN cwd_group G ON G.ID = M.parent_id
WHERE M.child_user_id IS NULL
and U.active='T';

Cheers,
Shawn 

[Mike]

Perfecto!