Oracle SQL hierarchical query with two tables

Question

I have two tables with the following structure

Table 1:

PRODID |   PSTID  |
___________________
 1     |    4
 2     |    
 3     |    2 
 4     |    
 5    |    

Table 2:

PSTID   |  PRODID
_______________
 1      |   4
 2      |   1
 3      |   1
 5      |   

• Now I can say that I start with PRODID 1 (could be any)

• It should get joined to table 2 with T1.PRODID = T2.PRODID and result in PSTID -> {2, 3}

• Now the corresponding T2.PSTID's are used to start the next round in table 1 with T2.PSTID = T1.PSTID => {3}

• Since now there is no more entry for PRODID = 3 in table 2 it should stop (but could go on)

Answer 1

SELECT t1.*
FROM   table1 t1
       INNER JOIN
       table2 t2
       ON ( t1.prodid = t2.prodid )
START WITH t1.prodid = 1
CONNECT BY PRIOR t2.pstid = t1.pstid;