Re: Modelling hierarchy in the relational database

From: Mikito Harakiri <nospam_at_newsranger.com>
Date: Tue, 04 Sep 2001 08:23:43 GMT
Message-ID: <jc0l7.2934$4z.4263_at_www.newsranger.com>

In article <ER_k7.2904$4z.4167_at_www.newsranger.com>, Vadim Tropashko says...

>Try printing out hierarchy like this:
>
>EMPNAME DNAME CBP
>------------------------------ -------------- ------------------------------
>**KING ACCOUNTING /KING
>****BLAKE SALES /KING/BLAKE
>******ALLEN SALES /KING/BLAKE/ALLEN
>******JAMES SALES /KING/BLAKE/JAMES
>******MARTIN SALES /KING/BLAKE/MARTIN
>******TURNER SALES /KING/BLAKE/TURNER
>******WARD SALES /KING/BLAKE/WARD
>
>with nested sets.

It can be done as follows. First, we need to build a view of each employee with names of their manager as a path like this:

SQL> SELECT P1.emp, P2.emp path, indentation

2     FROM Personnel_tree P1, Personnel_tree P2,
3          (SELECT count(P2.emp) indentation, P1.emp
4           FROM Personnel_tree P1, Personnel_tree P2
5           WHERE P1.lft BETWEEN P2.lft AND P2.rgt
6           GROUP BY P1.emp) counter

7 WHERE P1.lft BETWEEN P2.lft AND P2.rgt 8 and counter.emp = P2.emp
9 order by P1.emp, indentation;

EMP PATH INDENTATION
---------- ---------- -----------

Albert     Albert               1
Bert       Albert               1
Bert       Bert                 2
Chuck      Albert               1
Chuck      Chuck                2
Donna      Albert               1
Donna      Chuck                2
Donna      Donna                3
Eddie      Albert               1
Eddie      Chuck                2
Eddie      Eddie                3
Fred       Albert               1
Fred       Chuck                2
Fred       Fred                 3

The above query is no more than just a combination of Joe Celko's #1 and #4 "basic" queries.

Second, we need to group by the first column and use a concatenation function to aggregate by the second column (and ignore the third one as it is needed for ordering names in the path only). The query

select concat(path, '/') from (
SELECT P1.emp, P2.emp path
FROM Personnel_tree P1, Personnel_tree P2, (SELECT count(P2.emp) indentation, P1.emp FROM Personnel_tree P1, Personnel_tree P2 WHERE P1.lft BETWEEN P2.lft AND P2.rgt
GROUP BY P1.emp) counter
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
and counter.emp = P2.emp
order by P1.emp, indentation
) group by emp

could be executed by RDBMS supporting user-defined aggregates only, of course.

Finally, using your technique
http://www.oracle.com/oramag/code/tips2001/index.html?052001.html the query with user-defined aggregate could be implemented as:

SQL> SELECT CONCAT_LIST(EMPLS, '/') EMPLOYEES from (

2      SELECT 
3          emp, 
4          CAST(MULTISET(SELECT P2.emp  
5                  FROM Personnel_tree P1, Personnel_tree P2,
6                      (SELECT count(P2.emp) indentation, P1.emp
7                       FROM Personnel_tree P1, Personnel_tree P2
8                       WHERE P1.lft BETWEEN P2.lft AND P2.rgt
9                       GROUP BY P1.emp) counter
10                  WHERE P1.lft BETWEEN P2.lft AND P2.rgt
11                  and counter.emp = P2.emp
12                  and p1.emp = a.emp   
13                  order by indentation
14                  ) 
15          AS string_list_t) EMPLS 
16       FROM Personnel_tree a 
17       group by emp 

18 );

EMPLOYEES

---

/Albert
/Albert    /Bert
/Albert    /Chuck
/Albert    /Chuck     /Donna
/Albert    /Chuck     /Eddie
/Albert    /Chuck     /Fred

Received on Tue Sep 04 2001 - 10:23:43 CEST