Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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;
share|improve this answer

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'

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.