CODEWITHSUNDEEP
sqloracle-databasehierarchical-data
Robin clave
Robin clave

Reputation: 638

SQL Query to select parent hierarchy for a child

I have a table (dim_factory_relation) which stored the child factory IDs and the parent factory id for each child factories in the below format.

FK_CHILD_FACTORY_ID   FK_PARENT_FACTORY_ID
65                      5
66                      5
5                      13
10                     13
13                     25
13                     40
30                     40
40                    100
71                     56
72                     56
56                    100

Now, if I pass the lower child factory, it should provide the list of parents till the top level. Eg. I'm passing 65 as a child factory. It has to give me

FK_PARENT_FACTORY_ID
5
13
25
40
100

Please help how to get this using Oracle SQLs. Thanks for the help.

Upvotes: 0

Views: 1756

Answers (1)

vercelli
vercelli

Reputation: 4757

Use connect by prior

select FK_PARENT_FACTORY_ID
  from t
start with FK_CHILD_FACTORY_ID = 65
connect by prior FK_PARENT_FACTORY_ID=FK_CHILD_FACTORY_ID

OUTPUT

                   FK_PARENT_FACTORY_ID
---------------------------------------
                                      5
                                     13
                                     25
                                     40
                                    100

Upvotes: 3

Related Questions