How to write T-SQL Recursive Query to return hierarchical data using common self-joining table to store tree data

Question

Every time I try to revisit recursive queries I feel like I am starting over. I am wanting to query data from a table that stores hierarchical data using a common method, having a table that self-joins.

First of all, there is this table that stores "Groups", i think of as being "folders" using a Windows Explorer analogy. The ID is the PK and there is an associated group Name.

CREATE TABLE [dbo].[BPAGroup](
    [id] [varchar](10) NOT NULL,
    [name] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_BPAGroup] PRIMARY KEY CLUSTERED 
(
    [id] 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

Second and lastly, there is a relationship table that associates a group with its parent group. "GroupID" identifies "MemberID"'s group. For example, if you join up BPAGroupGroup.MemberID to [BPAGroup].ID, [BPAGroup].Name would contain the name of the group. if you join up BPAGroupGroup.GroupID to [BPAGroup].ID, [BPAGroup].Name would contain the name of the PARENT group.

CREATE TABLE [dbo].[BPAGroupGroup](
    [memberid] [varchar](10) NOT NULL,
    [groupid] [varchar](10) NOT NULL,
 CONSTRAINT [PK_BPAGroupGroup] PRIMARY KEY CLUSTERED 
(
    [memberid] ASC,
    [groupid] 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].[BPAGroupGroup]  WITH CHECK ADD  CONSTRAINT [FK_BPAGroupGroup_BPAGroup_groupid] FOREIGN KEY([groupid])
REFERENCES [dbo].[BPAGroup] ([id])
GO

ALTER TABLE [dbo].[BPAGroupGroup] CHECK CONSTRAINT [FK_BPAGroupGroup_BPAGroup_groupid]
GO

ALTER TABLE [dbo].[BPAGroupGroup]  WITH CHECK ADD  CONSTRAINT [FK_BPAGroupGroup_BPAGroup_memberid] FOREIGN KEY([memberid])
REFERENCES [dbo].[BPAGroup] ([id])
GO

ALTER TABLE [dbo].[BPAGroupGroup] CHECK CONSTRAINT [FK_BPAGroupGroup_BPAGroup_memberid]
GO

Here's some sample data in the form

Level1
   Level2
       Level3

and the SQL for the data

INSERT [dbo].[BPAGroup] ([id], [name]) VALUES (N'A', N'Level1')
INSERT [dbo].[BPAGroup] ([id], [name]) VALUES (N'B', N'Level2')
INSERT [dbo].[BPAGroup] ([id], [name]) VALUES (N'C', N'Level3')
INSERT [dbo].[BPAGroupGroup] ([memberid], [groupid]) VALUES (N'B', N'A')
INSERT [dbo].[BPAGroupGroup] ([memberid], [groupid]) VALUES (N'C', N'B')

How can I write a recursive T-SQL Server Query that returns all of the groups names, the recursion "level number" and IDs for all of the groups. Of course, the root of the tree would have a NULL ParentID and ParentName?

For example, these fields would be in the result set.

Level, GroupID, GroupName, ParentId, ParentName

I realize that there are multiple ways to store this type of data. I don't have flexibility to change the db design.

Ideally, the result should show all group names, even the root node, that doesn't have a parent.

Could you post some sample data and expected results please? I suspect I know the answer, however, I'd have to make some guesses on your data. — Thom A
– Thom A ♦, Commented Feb 23, 2019 at 19:41
Thank you. Sure, I'll make the GUID field a varchar and come up with all the SQL for the schema and data that you need.Working on it now.. — Chad
– Chad, Commented Feb 23, 2019 at 19:48

Thom A · Accepted Answer · 2019-02-23 20:22:23Z

2

Based on the latest data, this appears to get you the result you want:

WITH rCTE AS(
    SELECT 1 AS Level,
           id AS GroupID,
           [name] AS GroupName,
           CONVERT(nvarchar(10),NULL) AS ParentID, --This'll be uniqueidentifier in your real version
           CONVERT(nvarchar(255),NULL) AS ParentName
    FROM BPAGroup G
    WHERE NOT EXISTS (SELECT 1
                      FROM BPAGroupGroup e
                      WHERE e.memberid = G.id)
    UNION ALL
    SELECT r.Level + 1,
           G.id AS GroupID,
           G.[name] AS GroupName,
           r.GroupID AS ParentID,
           r.[GroupName] AS ParentName
    FROM BPAGroup G
         JOIN BPAGroupGroup GG ON G.id = GG.memberid
         JOIN rCTE r ON GG.groupid = r.GroupID)
SELECT *
FROM rCTE;

db<>fiddle

It's important you understand how this works though. As you said in your post, you seem to need to revisit these each time. There's nothing wrong with needing to check the syntax for something (there's some things I fail miserably at remembering sometimes, especially the new OPENJSON stuff), but do you understand how this works? If not, which bit don't you?

edited Feb 23, 2019 at 20:22

answered Feb 23, 2019 at 19:48

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Chad Over a year ago

Thanks for the answer. Please see my updated SQL to helpo you come quickly up with a working tables with sample data.

Thom A Over a year ago

Looks like I guessed the meaning of memberid and groupid the wrong way round @ChadD. Will test in a bit, but this might get you there beforehand

Chad Over a year ago

Thanks. I'm looking at it but I'm still mentally a long way off from getting this.

Thom A Over a year ago

@ChadD updated for you; I assume that's what you're after?

Thom A Over a year ago

Yes @ChadD, the datatypes in a UNION (ALL) need to be the same in all queries. As i had NULL values, they had to be cast/converted to the correct datatype. Guessing the sample we had wasn't representative of the live data's data types, and hence why you got the error.

|

Collectives™ on Stack Overflow

How to write T-SQL Recursive Query to return hierarchical data using common self-joining table to store tree data

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related