Oracle SQL Hierarchical Query to know path between two Elements

Question

                       1
                       |
      +----------------+-------------------+
      |                |                   |
      2                7                   9
      |                |                   |
   +--+--+             |                +--+--+
   |     |             |                |     |
   3     4             8               10     12
         |                              |
       +-+-+                            |
       |   |                            |
       5   6                           11 

Note : i could not post image so try to consider the numbers above as tree structure with 1 as the root node.

How can i use hierarchical query to get the path between two nodes

for eg : path between 11 and 4

i.e. output should be

11-10
10-9
9-1
1-2
2-4

Answer 1

You can start from the leaf and climb up:

select p_n || ' - ' || n
from t
where p_n is not null
start with n = 11
connect by prior p_n = n
order by level desc

Here is a sqlfiddle demo

---

EDIT: OK, this makes things a little bit more complicated...

You can climb up from both nodes but then you'll have to remove the duplicates paths (for example between 6 and 3 there is no need to go via the root 1)

try something like this:

select  the_path 
from
(select case when connect_by_root(n) = 11 then p_n || ' - ' || n else n || ' - ' || p_n end the_path,
 count(*) over (partition by n, p_n) cnt
from t
where p_n is not null
start with n in (11, 4)
connect by prior p_n = n)
where cnt = 1;

Here is another sqlfiddle demo