Re: A CTE Question

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 26 Aug 2010 21:09:11 +0200
Message-ID: <4C76BBD7.4050802_at_gmail.com>



On 26.08.2010 18:09, Michel Esber wrote:
> Hi Guys,
>
> 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
> group by R.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

You ask in the oracle group, your syntax however looks like you are working with mysql (i conclude it rather from inserts and ddl - not sure, whether mysql supports CTE) - the problem is, the query you are asking for will be probably differently implemented in both dialects, so , could you maybe clarify, do you need it in oracle or in some other dialect ?

Best regards

Maxim Received on Thu Aug 26 2010 - 14:09:11 CDT

Original text of this message