CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2016recursive-query
jdids
jdids

Reputation: 571

Get parent/children where parent numbers don't always match

I'm having an issue getting all related items to roll up into one result set.

In this instance, we have a master ParentPart (901359) with components that are related. We can also have components that are a parent part (340804, 340801, 340850)

The below data is what I'm dealing with

ParentPart  Component
---------------------
901359      340804
340804      340801
340801      340850
340850      333000

What I'm looking to get is this result, or to get all of the related components to roll-up

ParentPart  Component
----------------------
901359      340804
901359      340801
901359      340850
901359      333000

Below is code for testing.

CREATE TABLE #Hierarchy 
(
    ParentPart VARCHAR(15),
    Component VARCHAR(15)
)

INSERT INTO #Hierarchy (ParentPart, Component)
VALUES 
  ('901359','340804'),
  ('340804','340801'),
  ('340801','340850'),
  ('340850','333000')

 SELECT *
 FROM #Hierarchy

 DROP TABLE #Hierarchy

I've tried a recursive CTE, but it doesn't give me the result I'm looking for since the parent parts are not all the same.

Any pointers?

Upvotes: 0

Views: 45

Answers (2)

GMB
GMB

Reputation: 222482

That's a typical recursive query. In this case, you can walk the tree from the top to the bottom:

with cte as (
    select parentpart, component, 1 lvl  from #Hierarchy
    union all
    select c.parentpart, h.component, lvl + 1
    from cte c
    inner join #Hierarchy h on h.parentpart = c.component
)
select parentpart, component
from cte c 
where c.lvl = (select max(c1.lvl) from cte c1 where c1.component = c.component)

The recursive common table expression generates the tree paths, while keeping track of the level of each node; then the outer query filters on the top parent per node.

Demo on DB Fiddle:

parentpart | component
:--------- | :--------
901359     | 340804   
901359     | 340801   
901359     | 340850   
901359     | 333000

Upvotes: 2

Piotr Palka
Piotr Palka

Reputation: 3159

Following recursive CTE will do the work:

with hier as (
select ParentPart as MainPart, ParentPart, Component from #Heirarchy
union all
select hier.MainPart, p.ParentPart, p.Component 
from hier 
join #Heirarchy as p
on p.ParentPart = hier.Component 
)
select MainPart, Component from hier where MainPart = '901359';

Upvotes: 1

Related Questions