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

hierarchyoracle

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)?

Best Answer

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;
Related Question