April 15, 2013 at 10:11 am
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]
April 15, 2013 at 12:46 pm
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
April 16, 2013 at 9:16 am
So after gleaning that this problem may be solved by a recursive CTE I landed here.
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
April 18, 2013 at 3:22 pm
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
April 18, 2013 at 4:57 pm
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))
April 18, 2013 at 6:14 pm
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.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply