SQL & PL/SQL

Hi!

I have an hierarchical structure over two tables with the following fields:

Table Node

• node_id (id of the node)

Table Edge

• edge_id (id of the edge)
• src_node (source node)
• dst_node (destination node)

So two nodes are connected by one edge (-->) as in the following example:

N1 --> N2 --> N3 --> N4 --> N5

Given a Node (N5), I want to query for the node_id of the root node (N1). I tried to realize it by using 'connect_by_root', but I am struggling with the oracle examples on this.

In my case I want the two tables to be joined like below, so one row includes the node and the edge pointing on it:

SELECT
   t0. node_id
 , t1. src_node
 , t1. dst_node
 , t1. edge_id
FROM NODE t0
JOIN EDGE t1
   ON t0. node_id = t1. dst_node;

What I came up with (not the intended result):

SELECT
   level
 , t0.node_id
 , t1.src_node
 , connect_by_root t0.node_id as root_id
FROM NODE t0
JOIN EDGE t1
   ON t0.node_id= t1.dst_node
   connect by prior t1.src_node = t0.node_id;

Can you provide an example to implement my query?

Thanks in advance.