SQL & PL/SQL

Hi Everyone,

I want to write a hierarchy query which should give me the whole path starting from root node to each individual nodes by passing value of any individual tree member. below is the sample data and the output what i am expecting. and also the output what i am getting right now from my query.

CREATE TABLE RELATION (PARENT VARCHAR2(1),CHILD VARCHAR2(1) PRIMARY KEY);

--Data for the tree which starts from the root 'A'
Insert into RELATION (PARENT, CHILD) Values (NULL,'A');
Insert into RELATION (PARENT, CHILD) Values ('A', 'B');
Insert into RELATION (PARENT, CHILD) Values ('A', 'C');
Insert into RELATION (PARENT, CHILD) Values ('B', 'D');
Insert into RELATION (PARENT, CHILD) Values ('B', 'E');
Insert into RELATION (PARENT, CHILD) Values ('D', 'F');
Insert into RELATION (PARENT, CHILD) Values ('C', 'G');

--Data for the tree which starts from the root 'H'
Insert into RELATION (PARENT, CHILD) Values (NULL,'H');
Insert into RELATION (PARENT, CHILD) Values ('H', 'I');
Insert into RELATION (PARENT, CHILD) Values ('H', 'J');

Expected Output by passing values as 'C' which gives the whole tree where the node C is present:

A
A->B
A->C
A->B->D
A->B->E
A->C->G
A->B->D->F


My Query:

select
sys_connect_by_path(child,'->') tree
from
relation
--where (parent ='C' or child='C')
start with
parent is null
connect by
prior child = parent
order by tree;

Output of my query:

->A
->A->B
->A->B->D
->A->B->D->F
->A->B->E
->A->C
->A->C->G
->H
->H->I
->H->J

I am not able to add the condition for the query so that i can get only rows of the tree where the nod 'C' is present. i am just getting whole data from the table with all the unwanted trees.

Can anyone please help me in getting the correct output.

Thank you in advance.