Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Tree of nodes
valcindor_at_sinpag.com (valcindor) wrote in message news:<3e0d2ab1.0310030706.71e853f2_at_posting.google.com>...
> I have a table recording a tree of nodes.
> under each node there could be 0, 1 or more records representing
> documents.
> How can I recreate the tree of node with the number of documents
> attached
> to a node or its sub nodes ?
>
> ex :
> -- node-1
> document 1
> document 2
> -- node-1.1
> document 3
> -- node-2
> -- node-2.1
> document 4
> document 5
> document 6
> -- node-2.2
> document 7
> -- node-3
> ...
>
> the SQLRequest would present this tree of node like this :
> node-1 has 3 documents
> node-1.1 has 1 document
> node-2 has 4 documents
> node-2.1 has 3 documents
> node-2.2 has 1 document
> node-3 has 0 document
>
> The table structure is as follow
> create table toto ( obj_idy number, obj_peridy number, obj_type
> number, obj_name varchar(100));
Are you familiar with the 'connect by' clause? This can be handily done by first establishing a hierachical join between 2 copies of the same table. Resulting in something like
node-1 child1
node-1 child2
. . .
. . .
You can then simply do a group count on this virtual table.