Separate parent and child field from the same table on a row in the oracle

Asked

Viewed 46 times

0

I have a table that has a father-son relationship:

code product Cod father degree
1 banana pie 10 3
2 apple pie 11 3
10 banana 111 2
11 apple 111 2
111 fruit -9999999 1

I need to bring the fields "children" and their "ancestors". The table I use goes up to grade 4. I need a result similar to this:

Code Product Cod Pai1 pai1 Cod Pai2 pai2 name
1 Banana pie 10 Banana 111 Fruit
2 Apple pie 11 mace 111 Fruit
10 Banana 111 fruit -9999999
111 fruit -999999

Currently I can reach this result by connecting the table to itself. Ex:

SELECT * FROM TABELA1 TAB1 
   INNER JOIN TABELA1 PAI1 ON TAB1.CODPAI = PAI1.CODIGO
   INNER JOIN TABELA1 PAI2 ON PAI1.CODPAI = PAI2.CODIGO

But the table in which I need to do this query is very large and the result was very heavy. Is there any way I can get to the sample result without making this table link x same table?

  • It would not be the case to use a recursive query? Maybe I’ll help.

  • https://docs.oracle.com/database/121/SQLRF/queries003.htm#SQLRF52335

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.