Recursive query to find the Parent item

Question

I have the following question below when making the query in Oracle 10G.

I have the table med_item_avaliar that has in its structure items that reference other items within itself (Parents and Children).

When making the simple query is quiet, the above items are returned, however, when trying to make the query linking the item to be evaluated to the Results table, in that of Results I am not able to bring the item Father, only the son by the fact that his bond is giving for the children.

The query below refers to the table in question:

select 
 t.nr_sequencia nr_sequencia_tipo,  
 t.ds_tipo ds_tipo,  
 i.ds_item ds_item,  
 i.nr_seq_tipo,  
 i.nr_sequencia nr_sequencia_item,  --aqui fica o item   
 i.nr_seq_superior                  --aqui fica a posição acima 

from med_tipo_avaliacao t,   
     med_item_avaliar i  

where t.nr_sequencia = i.nr_seq_tipo  
and t.nr_sequencia = 2281; 

Below is the survey I’m trying to do looking for the items that were answered. The question is that the parents of these items are missing.

--select dos itens  
FROM
    med_avaliacao_paciente   a, -- aqui o cabeçalho da avaliação no atendimento  
    med_avaliacao_result     b, -- aqui o resultado da avaliação, liga-se com o cabeçalho  
    pessoa_fisica            c, -- aqui o cadastro da PF pra pegar o nome  
    med_tipo_avaliacao       d, -- aqui fica o tipo de avaliação  
    med_item_avaliar         e  -- aqui o item do tipo  

WHERE
    a.nr_sequencia = b.nr_seq_avaliacao  
    AND d.nr_sequencia = e.nr_seq_tipo           --ligar o tipo de avaliação ao item  
    AND d.nr_sequencia = a.nr_seq_tipo_avaliacao  -- ligar o tipo de avalização ao tipo de atendimento  
    AND b.nr_seq_item = e.nr_sequencia           -- ligar o item da pesquisa ao item respondido  
    AND b.nr_seq_avaliacao = 1377920  
    AND d.nr_sequencia = 2281  
    AND a.nr_atendimento = 2147535;  

This way I’m just taking the items that were answered, but I don’t know which is the Father of this item, only the item answered that varies between: Adequate, Inadequate, as the image below:

How to bring also the Father of the children in this consultation and who really corresponds to the children?

Answer 1

If the table is itself’s daughter (FK for the same table), just try to perform a subselect by taking the description field, referencing the subselect table’s PK with the parent table’s FK field.

(SELECT filha.ds_item
FROM med_item_avaliar filha
WHERE filha.nr_sequencia = pai.nr_seq_superior) item_superior,