Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Group of Groups


Group of Groups

Author
Message
Chrissy321
Chrissy321
SSC Eights!
SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)

Group: General Forum Members
Points: 818 Visits: 4614
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]
Chrissy321
Chrissy321
SSC Eights!
SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)

Group: General Forum Members
Points: 818 Visits: 4614
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
Chrissy321
Chrissy321
SSC Eights!
SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)

Group: General Forum Members
Points: 818 Visits: 4614
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
Chrissy321
Chrissy321
SSC Eights!
SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)

Group: General Forum Members
Points: 818 Visits: 4614
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
Erin Ramsay
Erin Ramsay
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 1099
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))


Sergiy
Sergiy
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6598 Visits: 11564
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search