A CTE Question

Can anyone shred some light here?

Sample DDL Code:

create table DEPARTMENT
(

DEP_ID integer not null primary key,
NAME VARCHAR(32) not null,
PARENT integer not null
) ;

insert into DEPARTMENT VALUES (1,'A',-2),(2,'B',1),(3,'C',2),
(4,'D',-2),(5,'E',4),(6,'F',5),(7,'G',6);

create table PERSON
(

PERSON_ID integer not null primary key,
NAME VARCHAR(128) not null,
DEP_ID integer,
constraint FK_01 foreign key (DEP_ID) references DEPARTMENT(DEP_ID) );

insert into PERSON values (1,'John',2),(2,'Mary',2),(3,'Suzan',3),
(4,'Joe',3),(5,'Lewis',3),(6,'Elvis',4),(7,'Presley',5),(8,'Preston',
5),(9,'Pee',7),(10,'Wee',7),(11,'Steve-o',7),(12,'Demi Moore',7);

with t_recurs (dep_id, parent, iter) as

(

select dep_id, parent, 0 from DEPARTMENT

union all

select r.dep_id, d.parent, iter + 1
from t_recurs r, DEPARTMENT d
where r.parent = d.dep_id and iter < 1000 )

select R.DEP_ID, COUNT(P.PERSON_ID)
from T_RECURS R inner join DEPARTMENT D on (R.DEP_ID, R.PARENT) =
(D.DEP_ID, D.PARENT)

                left outer join PERSON P on P.DEP_ID = D.DEP_ID

The output looks good:

DEP_ID DEP_COUNT
----------- -----------

          1           0
          2           2
          3           3
          4           1
          5           2
          6           0
          7           4

What am I actually looking for is also the count of Persons that are in all sub-departments in that hierarchy, including the departement a person is currently in. Somehow I can't get this to work.

Expected output:

DEP_ID DEP_COUNT HIERARCHY_COUNT
----------- ----------- ---------------

          1           0               5
          2           2               5
          3           3               3
          4           1               7
          5           2               6
          6           0               6
          7           4               4

Any ideas?

Thanks Received on Thu Aug 26 2010 - 11:09:51 CDT