Why database view is not working as expected?

[TT3]

I have following setup for a view. The purpose is to get list of users who belong to a specific group. However, it seems ServiceNow is completely ignoring the where condition "usr_group='4404aff51bd0f810ae9610a61a4bc..." and shows all the groups and users mapped to those groups. Any idea what is wrong here?

[Brad Bowman]

The purpose of a Database View is to join two or more tables so that you can view columns on all tables in the view.  You can join a table to itself, which is useful in some cases, but it looks like what you're trying to do is simply filter the group member table to only show a certain group, which can be done as a filter in list view and saved as a favorite, report,...  Is there something more you are trying to accomplish here?   

[Robert Campbell]

I agree with @Brad Bowman, you need to use more than one table.  I have tried to "build" my views and on the database, I would start with the one table to ensure I have the primary table the way I want and add the different tables to build my view step-by-step.  This doesn't work with ServiceNow database views.  

Here is an example of how I would accomplish this using the User, Group & Group Member tables:

Table	Order	Variable prefix	Where clause
ssy_user_grmember	100	gm	gm.user = u.sys_id
sys_user	200	u
sys_user_group	300	g	gm.group - g.sys_id && g.name = '<name of group>'

I guess you can change g.name to g.sys_id if you want to use the sys_id of that group rather than the name.