By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test
( id int identity(1,1) primary key,
parent_id int,
active varchar(1));
alter table test
add constraint test_parent_fk
foreign key (parent_id) references test(id);
insert into test(active) values ('Y');
insert into test(parent_id,active) values (1,'Y');
insert into test(parent_id,active) values (1,'N');
insert into test(active) values ('Y');
insert into test(parent_id,active) values (4,'N');
insert into test(active) values ('N');
insert into test(parent_id,active) values (6,'N');
7 rows affected
select t1.id,t1.parent_id,t1.active
from test t1 inner join test t2 on t1.parent_id=t2.id
where t1.active <> 'Y' and t2.active='Y';
| id | parent_id | active |
|---|---|---|
| 3 | 1 | N |
| 5 | 4 | N |