dm_group and Important Queries

Let us consider the groups and users as below. As shown below the Batman and Iron Man are groups and are part of the Movies group. Al Pacino and Marlon Brando are Users and are part of the Movies group. Robert Downey Jr. is a User part of the Iron man group and Christian Bale and Morgan Freeman are part of the Batman group.

1. Query for getting the Users directly linked to the Group

SELECT users_names 
	FROM dm_group 
	WHERE group_name = 'Movies'

Result

The Query will return Al Pacino and Marlon Brando

2. Query for getting the Users directly and indirectly linked to a Group

SELECT i_all_users_names 
	FROM dm_group 
	WHERE group_name = 'Movies'

Result

The Query will return Al Pacino, Marlon Brando, Christian Bale and Morgan Freeman

3. Query for getting the Groups directly linked to a Group

SELECT groups_names 
	FROM dm_group 
	WHERE group_name = 'Movies'

Result

The Query will return Iron Man and BatMan

4. Query for getting the Groups a user is part of (Directly and Indirectly)

SELECT group_name 
	FROM dm_group 
	WHERE ANY  i_all_users_names = 'Christian Bale'
	AND group_class = 'group'

Result

The Query will return Movies and BatMan