Build an Oracle query to get parent and child records Table structure as below


col1| col2 | col3|
 1  | empty|empty|
 2  |  1   | Xyz |
 3  |  1   | abc |
 4  |  2   | abc |


Note Col2 is deciding parent record for child record.

Looking to get result if we search for where col3='abc' we will get results as:


1  | empty|empty|    
2  |  1   | Xyz |
3  |  1   | abc |
4  |  2   | abc |


Table definition and sample data:

CREATE TABLE ForgeRock 
(
    id int, 
    Dependency varchar(7), 
    Prod varchar(55), 
    Prod2 varchar(22)
) ;

INSERT ALL
    INTO ForgeRock (id, Dependency, Prod, Prod2) 
        VALUES (1, '', '','')
    INTO ForgeRock (id, Dependency, Prod, Prod2) 
        VALUES (2, '1', 'Full','101')
    INTO ForgeRock (id, Dependency, Prod, Prod2) 
        VALUES (3, '1', 'Robust','101')
    INTO ForgeRock (id, Dependency, Prod, Prod2) 
        VALUES (4, '2', 'Robust','102') 
SELECT * FROM dual ;
Such hierarchical queries can be written using CONNECT BY as:

SQL&gt; set null "empty"
SQL&gt; select id, dependency, prod, prod2 from forgerock
     connect by prior dependency = id start with prod2 = '102' order by level desc;

        ID DEPENDE PROD       PROD2
---------- ------- ---------- ----------------------
         1 empty   empty      empty
         2 1       Full       101
         4 2       Robust     102