Oracle complex query with hierarchy

Following is my Sample data1

CP | NP |MC |DS |NNP
--- ---- --- -- ---
A  |  B |   |   |C
C  |  D | XX|YY |E
E  |  F | ZZ|11 |

Expected Output1

NP |MC |DS 
--- ---- --- 
 B | ZZ|11

Here column “CP” and “NNP” are hierarchical (i.e.) we will start with query like below, for a particular NP and then get the corresponding MC and DS values using the below logic

select MC, DS from tabl1
where NP=B

1. first look at value A from column CP and then see if column NNP as a corresponding value, here it is C value in column NNP
2. if there is a corresponding values form column CP present in NNP then look at column CP for C value and then see if NNP as corresponding value here it is E value in column NNP (repeated these steps until we do not find a corresponding entry in NNP column for column CP)
3. fetch E value corresponding values for column MC and DS

Following is my Sample data2

CP | NP |MC |DS |NNP
--- ---- --- -- ---
A  |  B | 96|KK |C
C  |  D | XX|YY |E
E  |  F | ZZ|11 |

Expected Output2 (if the corresponding root values are not null then we need to fetch those as well

NP |MC |DS 
--- ---- --- 
 B | 96|KK
 B | ZZ|11

Answer

Direct application of hierarchical (connect by) query:

select  connect_by_root(np) as np, mc, ds
from    sample_data
where   connect_by_isleaf = 1   -- OR:  nnp is null   (IF NEEDED, SEE BELOW)
start   with cp = 'A'
connect by cp = prior nnp
;

In your output, in column NP you have the value B, which is the value in the “root” row (the starting row). This is where you can use connect_by_root. All the other values are from the leaf row (“last” row in the hierarchy). Here is where we need to use the filter connect_by_isleaf = 1 – the value is 0 for non-leaf rows (rows that have at least one child), and 1 for leaf rows.

Note that “leaf” means no further children. This can happen either if NNP is null OR if it is not null but the value is not found in the CP column anymore. If you only want leaf rows where NNP is null, you can change the where clause as shown in the code above.

EDIT The OP requested a modification – the output should also include the root row, in addition to the leaf, but only if either the mc or the ds value (or both) is/are not null. This can be achieved by changing the where clause, which should be:

...
where (connect_by_isleaf = 1) 
   or (level = 1 and (mc is not null or ds is not null))
...