Parent child relation hierarchy (multiple children) - SQL Server

Question

I have a table with multiple child-parent relations. I want to get a hierarchical data set.

Table structure. (includes circular reference)

Parent   Child1 Child2
--====== ======== ========
0        1000     NULL
1000     1001     NULL
1001     1002     1003
1002     1004     1005
1003     1005     NULL
1004     1002     NULL
1005     1006     NULL

Data set I want is (Child2 - 1003 & 1005 becomes parent as they have their children but only up to the last child - no circular like Child1 - 1004 [remains child only])

Parent   Child
--====== ======== 
0        1000
1000     1001
1001     1002
1002     1003
1002     1004
1003     1005
1005     1006

Can anyone help please?

I tried the below code -

INSERT INTO @WorkflowStop (ParentId, ChildId,AdvChild, StopName)
                SELECT ISNULL(tWrkflwArrow.osoObjectTemplateProcessStepBeginning_otsID,0) AS ParentId,
                       ISNULL(tWrkflwArrow.osoObjectTemplateProcessStepEnding_otsID,0) AS ChildId,
                       ISNULL(tWrkflwArrow.osoObjectTemplateProcessStepAutoAdvance_otsID,0) AS AdvChild,
                       ISNULL(tWrkflwStop.otsInternalName,tWrkflwStop.otsExternalName) AS StopName
                FROM ABC.[dbo].[Workflow] AS tWrkflw
                INNER JOIN DEF AS tWrkflwStop
                    ON tWrkflwStop.otsObjectTemplateCategory_otcID = tWrkflw.otcID
                INNER JOIN GHI AS tWrkflwArrow
                    ON tWrkflwArrow.osoObjectTemplateProcessStepEnding_otsID = tWrkflwStop.otsID
                WHERE tWrkflw.otcID = @WorkflowDefinitionId 

                INSERT into @WorkflowStopHierarchy (ParentId, ChildId,AdvChild, StopName,[Level])
                SELECT tWrkflwStop.ParentId,
                       tWrkflwStop.ChildId,
                       tWrkflwStop.AdvChild,
                       tWrkflwStop.StopName,
                       0 as [Level]
                FROM @WorkflowStop AS tWrkflwStop
                WHERE tWrkflwStop.ParentId = 0;

                WHILE @@ROWCOUNT > 0
                BEGIN
                    INSERT INTO @WorkflowStopHierarchy (ParentId, ChildId,AdvChild, StopName,[Level])
                    SELECT tWrkflwStop.ParentId,
                           tWrkflwStop.ChildId,
                           tWrkflwStop.AdvChild,
                           tWrkflwStop.StopName,
                           tHierarchy.[Level] + 1 as [Level]
                    FROM @WorkflowStop AS tWrkflwStop
                    INNER JOIN @WorkflowStopHierarchy AS tHierarchy
                         ON (tWrkflwStop.ParentId = tHierarchy.ChildId) OR (tWrkflwStop.ParentId = tHierarchy.AdvChild)
                    WHERE (tWrkflwStop.ChildId NOT IN (select ChildId from @WorkflowStopHierarchy)) OR (tWrkflwStop.AdvChild NOT IN (select AdvChild from @WorkflowStopHierarchy))
                END;

                WITH CTE AS 
                (
                    SELECT tHierarchyChild.ParentId,
                           tHierarchyChild.ChildId,
                           tHierarchyChild.StopName,
                           ROW_NUMBER() OVER(PARTITION BY tHierarchyChild.ChildId ORDER BY tHierarchyParent.[Level], tHierarchyChild.parentId) as RowNumber
                    FROM @WorkflowStopHierarchy AS tHierarchyChild
                    LEFT JOIN @WorkflowStopHierarchy AS tHierarchyParent 
                        ON tHierarchyChild.ParentId = tHierarchyParent.ChildId
                )


                SELECT @WorkflowDefinitionId,
                       ParentId,
                       ChildId,
                       StopName
                FROM CTE
                WHERE RowNumber = 1
                ORDER BY ParentId,ChildId

Can you share the code you wrote and found to not solve the issue? — dfundako, Aug 13 '18 at 15:27
SELECT tWrkflw.otcID, ISNULL(tWrkflwArrow.osoObjectTemplateProcessStepBeginning_otsID,0) AS PARENTID, ISNULL(tWrkflwArrow.osoObjectTemplateProcessStepEnding_otsID,0) AS CHILDID, ISNULL(tWrkflwStop.otsInternalName,tWrkflwStop.otsExternalName), 0 as IsDone FROM ABC AS tWrkflw INNER JOIN DEF AS tWrkflwStop ON tWrkflwStop.otsObjectTemplateCategory_otcID = tWrkflw.otcID INNER JOIN [GHI AS tWrkflwArrow ON tWrkflwArrow.osoObjectTemplateProcessStepEnding_otsID = tWrkflwStop.otsID WHERE tWrkflw.otcID = 500003 — Sandipan, Aug 13 '18 at 15:31
You should update your question with the code you tried. Comments are horrific for code. — Sean Lange, Aug 13 '18 at 15:32
How do you know which branch of a circular reference like 1004 to leave out? It would be relatively easy to do if the higher-numbered reference back to a lower-numbered reference is the reference to ignore/remove, but is that correct, or is there some other criterion to be checked for which reference should be considered primary? — Laughing Vergil, Aug 13 '18 at 15:47
The answer you seek can be found here. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Aug 13 '18 at 15:48
@LaughingVergil.. 1004 does not have any child.. it is referencing back to 1002, so that is the last child. If that helps. — Sandipan, Aug 13 '18 at 15:53
Hi. This is unclear. Use enough words & phrases sentences to clearly say what you mean. Read & act on [mcve]. But it's going to be a faq. Always google many clear, concise, specific phrasings of your question/problem/goal/issue with & without your particular strings/names & read many answers. [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/q/20215744/3404097) — philipxy, Aug 13 '18 at 23:22

score 0 · Accepted Answer · answered Aug 14 '18 at 05:26

Here's one way to tackle it. I'm sure there are better.

drop table if exists #data;

create table #data(
    parent nvarchar(100) ,
    child1 nvarchar(100) ,
    child2 nvarchar(100)
);

insert  #data
values  ('0', '1000', null),
        ('1000', '1001', null),
        ('1001', '1002', '1003'),
        ('1002', '1004', '1005'),
        ('1003', '1005', null),
        ('1004', '1002', null),
        ('1005', '1006', null);

drop table if exists #paths;
select parent, child1 as child into #paths from #data where child1 is not null
union
select parent, child2 from #data where child2 is not null;

drop table if exists #results;
create table #results(parent nvarchar(100), child nvarchar(100));
insert  #results 
select  parent, child 
from    #paths 
where   parent not in (select child from #paths);
while @@rowcount > 0
    insert  #results 
    select  parent, child 
    from    #paths 
    where   parent not in (select parent from #results) 
            and child not in (select parent from #results) 
            and parent in (select child from #results);
select * from #results;

Parent child relation hierarchy (multiple children) - SQL Server

1 Answers1