User and Group database view join filter

[Bradley Koerner]

I have a database view that is a left join of Users, User Group, and Groups such that a user always shows up on the table. This is to be able to see which users are in a group and which are not. There is always at least one entry per user.

Table	Order	Variable	Where Clause	Left Join
sys_user	100	us		false
sys_user_grmember	200	m2m	us_sys_id=m2m_user	true
sys_user_group	300	gr	m2m_group=gr_sys_id	true

I now have a requirement to not include groups that are not active and don't have a certain value in a "type" field. Basically treat those groups as if they don't exist prior to joining.

I can't just filter on this table because if a group is inactive, the entire row won't show up, missing a user that is only part of one inactive group. If I do have a user that is only part of one inactive group, I want them to show up with no group association.

How can I filter my groups before left joining? I tried gr_active=true && m2m_group=gr_sys_id, but that is returning no results.

[Community Alums]

Hi Bradley,

I would suggest to just use the OOTB table sys_user_grmember and provide a list report with required columns from either Group/User.

You can then use the filter condition builder to remove user group membership where the group.active is false.

Let me know if this makes sense and resolves your data reporting requirements?

Thanks,

Enrique

[Bradley Koerner]

I'll explain why that won't work for my use case with an example:

User1 is not part of any groups

User2 is part of Group1 and Group 2, both groups are active

User3 is part of Group3, which is inactive.

If I report on sys_user_grmember and filter on group.active=false there are two issues:

- User1 won't show up because there is no record relating to User1 on sys_user_grmember table (thus the need for a left join).

- User3 won't show up because I am filtering out inactive groups, so that record would be completely ignored.

My desired results are:

User1	Null
User2	Group1
User2	Group2
User3	Null

I need every entry in sys_user to appear at least once, and I need inactive groups and groups that don't match a certain type to essentially not exist when joining. I can't filter after the join because then I will be removing entries that need to be there.