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.
– Jean Barbosa
https://docs.oracle.com/database/121/SQLRF/queries003.htm#SQLRF52335
– Motta