Re: Database planning - Hierarchical problem - Help needed

this example works on Oracle 10g (should work also on Oracle 9i):

• Creating some demo tables:

CREATE TABLE PERSONS (
    ID NUMBER,
    NAME VARCHAR2(20),
  PRIMARY KEY (ID)); CREATE TABLE RELATIONS (
    PARENT_ID NUMBER,
    CHILD_ID NUMBER);

• Creating some demo data as in your example:

INSERT INTO PERSONS (ID,NAME) VALUES (1,'Chuck'); 
INSERT INTO PERSONS (ID,NAME) VALUES (2,'John'); 
INSERT INTO PERSONS (ID,NAME) VALUES (3,'Albert'); 
INSERT INTO PERSONS (ID,NAME) VALUES (4,'Bert'); 
INSERT INTO PERSONS (ID,NAME) VALUES (5,'Donna'); 
INSERT INTO PERSONS (ID,NAME) VALUES (6,'Eddie'); 
INSERT INTO PERSONS (ID,NAME) VALUES (7,'Fred'); 
INSERT INTO PERSONS (ID,NAME) VALUES (8,'Gilbert'); 
INSERT INTO RELATIONS (PARENT_ID,CHILD_ID) VALUES (1,2); 
INSERT INTO RELATIONS (PARENT_ID,CHILD_ID) VALUES (1,3); 
INSERT INTO RELATIONS (PARENT_ID,CHILD_ID) VALUES (1,5); 
INSERT INTO RELATIONS (PARENT_ID,CHILD_ID) VALUES (1,6); 
INSERT INTO RELATIONS (PARENT_ID,CHILD_ID) VALUES (1,7); 
INSERT INTO RELATIONS (PARENT_ID,CHILD_ID) VALUES (3,4); 

• And then this SELECT will return the following result:

SELECT SYS_CONNECT_BY_PATH(pp.name, '/')||'/'

       ||(SELECT pc.name FROM PERSONS pc WHERE pc.id=r.child_id)   FROM RELATIONS r,

       PERSONS pp	  
	 WHERE pp.id=r.parent_id	   

/Chuck/John
/Chuck/Albert
/Chuck/Albert/Bert
/Chuck/Donna
/Chuck/Eddie
/Chuck/Eddie/Gilbert
/Chuck/Fred
/Chuck/Fred/Gilbert
/Albert/Bert
/Eddie/Gilbert
/Fred/Gilbert
-----------------

Regards, Jan

philippe.lecomte_at_step.fr (Philippe) wrote in message news:<ee069cd0.0411290350.474746ae_at_posting.google.com>...
> Hi,
>
> I'm actually on a problem i don't manage to resolve.
> I want to implement a thing like this in a table :
>
> Albert John
> / \ /
> / \ /
> Bert Chuck
> / | \
> / | \
> / | \
> / | \
> Donna Eddie Fred
> \ /
> \ /
> \ /
> Gilbert
>
>
>
> All these links are work relationship between employees...
> So for example, Bert superior is Albert.
> And Chuck has two superiors : Albert and John
>
> What's in ur point the best method to organize this in a table and do
> simple queries on it ?
>
> I tried a modified preorder tree traversal algorithm but since it's
> not hierarchical anymore, i don't manage to get it to work... (some
> item have two superiors)
>
> Any help welcome... (here or by direct email contact)
> philippe.lecomte_at_step.fr
>
> Thnks in advance,
> Philippe
Received on Wed Dec 01 2004 - 14:45:47 CET