quote:

---

Originally posted by consultor.dwh

Maybe recurseive CTE is overkill. You could try to create a query with parent and grandparent. Something like this:

select gp.childId gparent
,gp.flag gparent_flag
,p.childid parent
,p.flag parent_flag
,c.childid child
from table c
join table p
on c.parentid = p.childid
join table gp
on p.parentid = gp.childid

Then you can find in this query if your child row has parent and grand parent rows active.

This query is using "Join", so, only retrieves rows with parents and grand parents. If orphan rows apply to your requerment, then you will need to change the join to LEFT JOIN

---

quote:

---

Originally posted by nimda

Guys,

I have a recursive table where every record has a child ID, a parent ID and an active flag.
If I take a record from, let's say third level I have to check that its parents at level one and two are active. If they aren't I shouldn't return anything.
I struggled somewhat with a recursive CTE, but not to the point that I solved this question.
Ideas anyone?

Nimda

---

create table #hierarchy
(
childid int identity(1,1),
parentid int,
active bit
)

insert #hierarchy (parentid,active)
values(NULL,1),
(1,1),
(null,1),
(null,1),
(3,1),
(5,1),
(2,1),
(7,0)
declare @child int
set @child=8--pass any value here
;with cte (id,parent,active)
as
(select *
from #hierarchy
where childid=@child
union all
select h.*
from cte c
join #hierarchy h
on h.childid = c.parent
)
select *
from
(
select * ,COUNT(case when active=0 then 1 else null end) over() as cntinact
from cte
)t
where cntinact=0

with UnitResolved (unit_id, parent_id, unit_name, start_date, end_date, is_deleted, depth) as 
(
	select 
		ou.unit_id
		, ou.parent_id
		, ou.unit_name
		, ou.start_date
		, ou.end_date
		, ou.is_deleted
		, 0 [Depth]
	from dbo.organizational_units ou
	where ou.parent_id = '-1'
	union all
	select 
		ou.unit_id
		,ou.parent_id
		,ou.unit_name
		, ou.start_date
		, ou.end_date
		, ou.is_deleted
		, Depth+1
	from unitresolved ur
	join organizational_units ou on ou.parent_id = ur.unit_id
)

--drop table organizational_units_resolved
select distinct *
into organizational_units_resolved
from UnitResolved
OPTION (MAXRECURSION 10000)