A connect by query on all children

Question

Is there any way to have the connect prior... start with statement in Oracle to start with the first "child" in the table and then run the query for all the remaining children as well? I know how to use the statement for 1 child and get its ancestry, but I need the code to run for lots of different children in the table.

Let's suppose I have this code which returns a Component and Component's Color from table of components and the Component's parent and that is classified as a "Sample" and its color where component's parent ID is the ID of the parent for the component.

This is my schema:

Components table:
Component        Component_ID      Component_Parent_ID      Component_Label

Component_Color Table:
Component      Component_Color

Note: this is example code, not the one I am actually using, so if you silly errors, I am very sorry.

SELECT Component,
  FROM Components
 INNER JOIN Component_Color ON (Components.Component =  Component_Color.Component)
 WHERE Component_Label = 'Sample'
connect by prior  Component_Header.Component_Parent_ID = Component_Header.Component_ID
  start with Component.Component_ID = '2000'; 

Table Component has a lot of different components and I want the query to return not only the component with ID 2000 and its "sample" parent, but do this for all the children in the table "Components".

I can see how this can be done with a loop, but I cannot find what type of loop I can use for Oracle. Sorry, just starting.

Answer 1

Avoid using WHERE and ORDER BY clauses in hierachical queries, because they run after the hierarchical processing. Here I think you should use START WITH component_label = 'Sample'.

 SELECT level, components.component_id, components.component_label, component_color.component_color
   FROM compenents JOIN component_color ON components.component = component_color.component
  START WITH components.component_label = 'Sample'
CONNECT BY PRIOR components.component_id = components.component_parent_id