Recursive CTE Duplication
-
Hi all,
I am working on a system that has Business units and a Parent\Child relationship.
I am trying to write a CTE that will show me all of the descendants for any given BU. I feel i am nearly there.
WITH BU_CTE
AS (select BU.businessunitid , Bu.parentbusinessunitid, BU.businessunitid as TopBU
from businessunitbase BU
UNION ALL
select BU.businessunitid , Bu.parentbusinessunitid ,BU_CTE.businessunitid as TopBU
from businessunitbase BU
INNER JOIN
BU_CTE on BU_CTE.businessunitid = BU.parentbusinessunitid)
select * from bU_CTE
where topbu = '5A9E34D5-2374-E511-80D4-005056B22F14'
order by 1,2,3So this kind of works. Given any TopBU (which may not actually be the top of the entire BU hierarchy) it will show "businessunitID" that a descendants. The only thing i cannot work out is that its giving me duplicates. So if on one example i am expecting 9 rows but i am getting 33. If i DISTINCT the 33 rows i end up with the 9. The entire rows are complete duplicates (not just the BusinessunitID). So other than the top most record i am looking at the remaining 8 are duplicated 4 times.
Any idea what i am doing wrong? I just cannot see it.
Dan
-
Couldn't you just left join the table businessunitbase to itself?
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret. -
Hi,
Thanks for your reply.Sorry if i am not being clear. I need to get all the descendants and not just the children. So i need the children's children, and the children's children's children and so on (up to 10 levels). So basically i need to know this record had 8 children, 16 Grandchildren, 32 G-Grandchildren and so on.
A left join without any recursiveness would only bring back the children rather than all descendants unless you can think of a way of doing that?
Dan
-
A definition and test data for businessunitbase would be helpful.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret. - danielfountain - Wednesday, March 27, 2019 8:21 AM
For starters, the criteria for the TopBU needs to be in the first SELECT inside the CTE. It it's truly and "Adjacency List" (parent/child relationship table), there will be no node that has more than one parent nor will any child node be listed more than once and moving the criteria to where it belongs will totally eliminate the need for the use of DISTINCT.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi,
Just to clarify. For this odd example I want the nodes shown more than once. So the top most node will show every node. The second layer will show every node under the second layer node. So the nodes will be duplicated under each parental level. I hope that makes sense.
I am trying to currently create some statements to illustrate but i cant seem to get them to work at the moment 🙂
Dan
-
To help illustrate my issue.....
So here is an example data set:
create table #TEMP
(ID varchar(10),
Parent Varchar(10)
)Insert into #TEMP
values('1A',NULL),
('2A','1A'),
('3B','1A'),
('4D','2A'),
('5E','3B'),
('6F','5E'),
('7G','5E'),
('8G','5E');And here is the dataset i am hoping to get out of it (I hope i have done this correct as i have done it manually). To have the node listed with them self as a descendant is preferable but not required. Effectively i expect to be able to select any BU and it tell me a list of all of the BU`s below that one.
TOPBU Decendant 1A 1A 1A 2A 1A 3B 1A 4D 1A 5E 1A 6F 1A 7G 1A 8G 2A 2A 2A 4D 3B 3B 3B 5E 3B 6F 3B 7G 3B 8G 4D 4D 5E 5E 5E 6F 5E 6F 5E 7G 5E 8G 6F 6F 7G 7G 8G 8G For the above example my current code is the following
WITH BU_CTE
AS (select ID , Parent, ID as TopBU
from #TEMP BU
UNION ALL
select BU.ID , Bu.Parent ,BU_CTE.ID as TopBU
from #TEMP BU
INNER JOIN
BU_CTE on BU_CTE.ID = BU.Parent)
select * from BU_CTENow this isn't working. So effectively i what i am aiming for is the TOPBU to give me the TopBU that i am intrested in, and the ID would list the descendants. So i am getting duplicate results but if i de-dupe its missing lines. I cant quite work out what i am doing wrong.
Hope this illustrates better - and thank you for all the replies to date.
Dan
- Is this what you're after?
WITH CTE AS (
SELECT ID as Descendent,ID AS TOPBU,Parent
FROM #TEMPUNION ALLSELECT c.Descendent,t.ID,t.Parent
FROM #TEMP t
INNER JOIN CTE c ON c.Parent = t.ID
)
SELECT TOPBU, Descendent
FROM CTE
ORDER BY TOPBU, Descendent;____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark,
That looks to be very much what i need. I am currently in the process of trying to understand the differences.
Thank you so much for your help
Dan
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply