SQL child parent hierarchy - Using a where on child to hide parent-CodePudding

Let's say I have table below:

ID | Name | Active | ParentID 
1  | Foo1 | 1      | 0
2  | Foo2 | 1      | 1 
3  | Foo3 | 1      | 2
4  | Foo4 | 1      | 3
5  | Foo5 | 1      | 3
6  | Foo6 | 0      | 5
7  | Foo7 | 1      | 2
7  | Foo7 | 1      | 6
8  | Foo8 | 1      | 7
9  | Foo9 | 1      | 5

(I have indeed duplicate ID's, on which I expressed my thoughts but to no result)

As you can see, once child can have multiple parents. ID's with ParentID 0 have no parent. I need to select all ID's that are active and do not have an inactive parent above them, however high in the tree that might be.

So with the data set above, my result would be:

ID | Name | 
1  | Foo1 |
2  | Foo2 | 
3  | Foo3 |
4  | Foo4 |
5  | Foo5 |
9  | Foo9 |

ID 6 got removed because it was Inactive
ID 7 got removed because one of its parents (6) is inactive
ID 8 got removed because a parent (6) of its parent (7) is inactive
ID 9 is fine because its parent (5) is active and so are 5 his parents etc

I attempted this with a subquery in the where

SELECT *
FROM table
WHERE ID not in (SELECT ID FROM table where Active = 0)

But that only solves it for the current record.

I've also tried a typical self-join as used for employee/manager, but that only goes one layer deep, while here I also need to check for the parent of the parent etc

Any suggestions/ideas?

CodePudding user response：

One method would be to use an rCTE to work through the hierachy, with a column that retains the initial ID. Then you can use an EXISTS to ensure there are no rows with a value of 0 for Active:

WITH rCTE AS(
    SELECT ID,
           Name,
           Active,
           ParentID,
           ID AS InitialID
    FROM dbo.YourTable YT
    UNION ALL
    SELECT YT.ID,
           YT.Name,
           YT.Active,
           YT.ParentID,
           r.InitialID
    FROM rCTE r
         JOIN dbo.YourTable YT ON r.ParentID = YT.ID)
SELECT *
FROM dbo.YourTable YT
WHERE NOT EXISTS (SELECT 1
                  FROM rCTE r
                  WHERE r.InitialID = YT.ID
                    AND r.Active = 0);

CodePudding user response：

I would use a recursive CTE to identify IDs where the chain is continuous, using both conditional and unconditional increment by 1 as follows:

With A As 
(Select ID, [Name], Active, ParentID, 0 As NUM_1, 0 As NUM_2
 From Tbl Where ParentID=0
 Union All 
 Select Tbl.ID, Tbl.[Name], Tbl.Active, Tbl.ParentID,
        NUM_1   1 As NUM_1,
        NUM_2   IIF(Tbl.Active=1,1,0) As NUM_2
 From Tbl Inner Join A On (Tbl.ParentID=A.ID)
)
Select ID, [Name]
From A
Where ID Not In (Select ID From A Where NUM_1<>NUM_2)
Order by ID

Result:

ID	Name
1	Foo1
2	Foo2
3	Foo3
4	Foo4
5	Foo5
9	Foo9

db<>fiddle