SQL & PL/SQL

Hello,

Does anyone have a solution to find parent/child relationship for data more than 2 levels deep?

I have a solution when there's a simple parent-child relationship but not when there's a grandparent-parent-child relationship or deeper.

Ex. I have a table company_parent_child that stores the relationship betwen a company and it's direct parent.

create table TEMP_COMPANY_PARENT_CHILD
(
PARENT_ID NUMBER(10),
COMPANY_ID NUMBER(10)
);

insert into TEMP_COMPANY_PARENT_CHILD values (1, 10);
insert into TEMP_COMPANY_PARENT_CHILD values (1, 11);
insert into TEMP_COMPANY_PARENT_CHILD values (1, 12);
insert into TEMP_COMPANY_PARENT_CHILD values (2, 13);
insert into TEMP_COMPANY_PARENT_CHILD values (10, 100);
insert into TEMP_COMPANY_PARENT_CHILD values (10, 101);
insert into TEMP_COMPANY_PARENT_CHILD values (10, 102);
insert into TEMP_COMPANY_PARENT_CHILD values (11, 103);


1->
___10->
______100,101,102,
___11->103

Companies 100, 101 and 102 are under parent 10 and grandparent 1.

I need to create such a view or another temp table so that when I pass the parent ID, I will pull all the children on all levels. In addition, and this is the tricky part, when I join this new temp table or view to another data table without any parameters, the data should not be duplicate, ie. each company ID should appear only once.

create table TEMP_JOIN
(
company_id number(10),
order_id varchar2(10)
);

insert into TEMP_JOIN values (100, 'a');
insert into TEMP_JOIN values (101, 'b');
insert into TEMP_JOIN values (102, 'c');
insert into TEMP_JOIN values (103, 'd');
insert into TEMP_JOIN values (10, 'e');
insert into TEMP_JOIN values (11, 'f');
insert into TEMP_JOIN values (12, 'e');
insert into TEMP_JOIN values (13, 'f');

Thanks.