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