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

Mike_pfalli

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:

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

Having issues adding or similar:

 where ACTIVE = 'T';

Any help would be greatly appreciated. Thanks 

Confluence 6.6.6

Shawn C

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