By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Drop Table Tbl
Msg 3701 Level 11 State 5 Line 1
Cannot drop the table 'Tbl', because it does not exist or you do not have permission.
Create Table Tbl (ID int, [Name] VarChar(5), Active Int, ParentID Int)
Insert Into Tbl (ID, [Name], Active, ParentID)
Values (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)
10 rows affected
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
| ID | Name |
|---|---|
| 1 | Foo1 |
| 2 | Foo2 |
| 3 | Foo3 |
| 4 | Foo4 |
| 5 | Foo5 |
| 9 | Foo9 |