CODEWITHSUNDEEP
sql-serverdatabase-design
jgauffin
jgauffin

Reputation: 101166

Recursive select

I'm tried to create a SQL statement using WITH but I can't get it right.

I got the following tables;

Groups (Id, Name)

1, 'My app'
2, 'Local admins'
3, 'Global admins'

Users (1, Name)

1, 'Arne'

GroupOwners (GroupId, OwnerType, OwnerId)

1, 'Group', 2

GroupMembers (GroupId, MemberType, MemberId)

2, 'Group', 3
3, 'User', 1

I'm trying to find all groups that Arne is a owner of. In this case it's My App. But the thing is that Local Admins is set as the owner. And Global admins is a member of Local admins. And Arne is finally a member of Global admins.

enter image description here

never mind the diamonds, they are incorrect

The nesting varies from group to group. (Some have the user directly in GroupOwners while others may have one group with users as members)

Is it possible to solve with one mother of SQL statement? Additional constraint: Groups/Users are used elsewhere in the system.

Upvotes: 3

Views: 211

Answers (2)

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

This is recursive cte that will first find groups Arno is a member of and then match all the groups that directly or indirectly contain those groups. Results are finally joined to GroupOwners to limit results. Note: if Arno might be owner of a group, but not a member of it, this query will need UNION to append these groups also.

declare @UserID int = 1

; with allTheGroups as (
  select gm.GroupID
    from GroupMembers gm
   where gm.MemberType = 'User'
     and gm.MemberID = @UserID
  union all
  select gm.GroupID
    from GroupMembers gm
   inner join allTheGroups
      on gm.MemberID = allTheGroups.GroupID
   where gm.MemberType = 'Group'
)
select Groups.*
  from Groups
 inner join GroupOwners gow
    on Groups.ID = gow.GroupID
   and gow.OwnerType = 'Group'
 inner join allTheGroups a
    on gow.OwnerID = a.GroupID

Sql Fiddle with example is here.

Example union to retrieve users who directly own a group added. Needs to be appended to query above (and select list tweaked, of course).

union
select gow.OwnerID, Users.Name
  from GroupOwners gow
 inner join Users
    on gow.OwnerID = Users.ID
   and gow.OwnerType = 'User'
 where gow.OwnerID = @UserID

Upvotes: 4

Zoran Pavlovic
Zoran Pavlovic

Reputation: 1232

Yes, your tables are modeled incorrectly. You need to have two tables only. Groups and Admins(or Users, depending on your scenario).

Then you store stuff like "Local" or "Global" or the name (Arne) in the Admin/User table. Then it's a trivial select or join between only two tables. No nesting/recursion necessary.

Let me know if you need me to draw you an ERD or something to explain.

Upvotes: -2

Related Questions