Oracle hierarchical query question (start with ... connect by ... )

Question

From the Oracle documentation:

To find the children of a parent row, Oracle evaluates the PRIOR expression of the CONNECT BY condition for the parent row and the other expression for each row in the table. Rows for which the condition is true are the children of the parent. The CONNECT BY condition can contain other conditions to further filter the rows selected by the query.

When Oracle select child node for a parent node, it selects from all rows except the current row right? Can a row appear multi times in the result set (I mean a row is both the child node of row A and row B)?

Answer 1

There would have to be data indicating that the row is both a child of row A and a child of row B. This can be done, but does not seem like it would be typical. Think of an employee. Most employees only have one direct managers. In a simple design an employee could have two managers, but it would require multiple employee records. For example:

--drop table e;
create table e (id number, name varchar2(100), manager number);
insert into e values (1,'Amos',null);
insert into e values (2,'Bob',1);
insert into e values (3,'Cindy',1);
insert into e values (4,'Dave',2);
insert into e values (4,'Dave',3);
select substr(name,1,7) name, substr(prior name,1,7) Manager
   from e start with id=1 connect by prior id=manager order by name;

Answer 2

I'm not clear is this is a question about modelling or the hierarchical query itself - but assuming the latter.

Can a row appear multi times in the result set (I mean a row is both the child node of row A and row B)?

yes, as long as there is no loop in the data, there is no restriction on the way you define what is a 'parent' row and what is a 'child' row:

testbed:

create table t as select level as id from dual connect by level<4;
select * from t;

ID                    
----------------------
1
2
3

example:

select prior id, id, level from t start with id=1 connect by prior id < id;

PRIORID                ID                     LEVEL                  
---------------------- ---------------------- ----------------------
                       1                      1                      
1                      2                      2                      
2                      3                      3                      
1                      3                      2                      

note that in the result, id 3 has two different 'parents'