By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
id parent_id active
1 NULL Y
2 1 Y
3 1 N
4 NULL Y
5 4 N
6 NULL N
7 6 N
Msg 102 Level 15 State 1 Line 1
Incorrect syntax near 'active'.
with cte as
(
select 1 as id, null as parent_id , 'Y' as active union all
select 2,1,'Y' union all
select 3,1,'N' union all
select 4,null,'Y' union all
select 5,4,'N' union all
select 6,null,'N' union all
select 7,6,'N'
) select * from cte where
parent_id in (
select id from cte where active='Y'
) and active='N'
| id | parent_id | active |
|---|---|---|
| 3 | 1 | N |
| 5 | 4 | N |