Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Group of Groups Expand / Collapse
Author
Message
Posted Monday, April 15, 2013 10:11 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 645, Visits: 3,763
Hello All,

Thanks if you would like to help.

I am hoping there is a 'best practice' way to solve this problem but searching 'sql server groups' gets a lot of results.

I have two tables, groups and group members. My goal is to write a function that when passed a group code will return all the group members.

The complicating factor is when a group contains other groups. I'd like to allow for multiple levels of groups.

I am certainly open to revising my table structures at this point. I expect dozens of groups and thousands of group members. I also have a Members table (not shown) if that helps.

Passing C should get the members 1,2,3,4
Passing D should get 4,1,2
Passing E should get 1,2,3,4,5


IF OBJECT_ID('[dbo].[GroupMembers]') IS NOT NULL DROP TABLE [dbo].[GroupMembers]
IF OBJECT_ID('[dbo].[Groups]') IS NOT NULL DROP TABLE [dbo].[Groups]

CREATE TABLE [dbo].[Groups]
(
[GroupCode] [varchar](20) NOT NULL,
[GroupDescription] [varchar](20) NULL
CONSTRAINT [PK_Groups] PRIMARY KEY CLUSTERED ([GroupCode] ASC)
)


CREATE TABLE [dbo].[GroupMembers](
[GroupCode] [varchar](20) NOT NULL,
[Member] [varchar](20) NOT NULL,
[MemberType] [varchar](12) NOT NULL
CONSTRAINT [PK_GroupMembers] PRIMARY KEY CLUSTERED
(
[GroupCode] ASC,
[Member] ASC,
[MemberType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


ALTER TABLE [dbo].[GroupMembers] WITH CHECK ADD CONSTRAINT [FK_GroupMembers_Groups] FOREIGN KEY([GroupCode])
REFERENCES [dbo].[Groups] ([GroupCode])
GO

INSERT INTO Groups (GroupCode,GroupDescription) VALUES ('A','Simple group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES ('B','Simple group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES ('C','Group of Groups')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES ('D','Mixed')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES ('E','Complicated')

INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('A','1','Individual')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('A','2','Individual')

INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('B','3','Individual')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('B','4','Individual')

INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('C','A','Group')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('C','B','Group')

INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('D','A','Group')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('D','4','Individual')

INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('E','C','Group')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('E','5','Individual')


SELECT * FROM [dbo].[Groups]
SELECT * FROM [dbo].[GroupMembers]
Post #1442392
Posted Monday, April 15, 2013 12:46 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 645, Visits: 3,763
This is what I have tried but this won't handle multiple levels. Is this the recursion rabbit hole?


DECLARE @GroupCode varchar(20)
SET @GroupCode = 'A'

SELECT DISTINCT
Member
FROM
(
SELECT
Member
FROM Groups G
INNER JOIN GroupMembers GM
ON G.GroupCode = GM.GroupCode
WHERE
MemberType = 'Individual' AND
G.GroupCode = @GroupCode

UNION

SELECT
Member
FROM GroupMembers
WHERE
GroupCode IN
(
SELECT
Member
FROM GroupMembers
WHERE
MemberType = 'Group' AND
GroupCode = @GroupCode
)

) dataset
Post #1442455
Posted Tuesday, April 16, 2013 9:16 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 645, Visits: 3,763
So after gleaning that this problem may be solved by a recursive CTE I landed here.

http://stackoverflow.com/questions/6224564/flattening-out-a-group-membership-tree-in-sql-with-cyclic-references

The following seems to work. I am still studying this solution since it is on the edge of my understanding.

DECLARE @GroupCode varchar(20)
SET @GroupCode = 'E'


;WITH Members AS
(
--Anchor

SELECT
GroupCode,
Member,
0 As isCycle,
'.' + CAST(Member As varchar(max)) + '.' As [path]
FROM dbo.GroupMembers
WHERE
Member NOT IN (Select GroupCode from GroupMembers)

UNION ALL

--Recursive call

SELECT
h.GroupCode,
h.Member,
CASE WHEN m.[path] like '%.' + CAST(h.Member as varchar(max)) + '.%' THEN 1 ELSE 0 END As isCycle,
m.[path] + CAST(h.Member as varchar(max)) + '.' As [path]
FROM GroupMembers h
INNER JOIN Members m
ON h.member = m.GroupCode
WHERE
isCycle = 0
)

SELECT
LEFT(REPLACE(path,'.',''),1) AS Member,
*
FROM
Members
WHERE
Members.isCycle = 0 AND
@GroupCode = GroupCode
Post #1442803
Posted Thursday, April 18, 2013 3:22 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 645, Visits: 3,763
Am I over complicating the string extraction? I want the text between the first and second occurrences of '.'

DECLARE @paths TABLE(path varchar(50))
INSERT INTO @paths
SELECT '.ab.qwer.poi' UNION
SELECT '.1.qwerty.ab' UNION
SELECT '.1234567890.asd.a'


SELECT
path,

LEFT
(
RIGHT
(
path,LEN(path)-1
)
,
charindex('.',RIGHT(path,LEN(path)-2)
)
) AS Member

FROM @paths
Post #1444168
Posted Thursday, April 18, 2013 4:57 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 4:43 PM
Points: 537, Visits: 1,023
I'm out for the night but this might get you started.. it will get all the text between the first '.' and the last '.'

NOTE: This is different than the first and second as it will still do first and last even if there are more than 2 '.' in a string.

declare @textfield varchar(255)

set @textfield = '123.qwerty.abc'

select substring(@textfield,charindex('.',@textfield),len(@textField)-charindex('.',reverse(@textfield))- charindex('.',@textfield))

Post #1444190
Posted Thursday, April 18, 2013 6:14 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 12:22 AM
Points: 4,570, Visits: 8,332
Chrissy321 (4/18/2013)
Am I over complicating the string extraction? I want the text between the first and second occurrences of '.'

DECLARE @paths TABLE(path varchar(50))
INSERT INTO @paths
SELECT '.ab.qwer.poi' UNION
SELECT '.1.qwerty.ab' UNION
SELECT '.1234567890.asd.a'


See if it will work for you:

SELECT
path, REVERSE(PARSENAME(REVERSE(path) + ' ', 2)), PARSENAME(path, 3)
-- " + ' '" is added to avoid "NULL" object names when there is nothing in front of the 1st dot.
FROM @paths

Both options in my query return the same values from your sample data.
Check which of them better fits the logic using to build the strings in the table.
Post #1444196
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse