How can I execute a query for all nodes between two nodes in a tree?

Question

I have a hierarchical database structure, for example. columns IDand PARENT_IDdefined for each row, with top level rows having NULL PARENT_ID.

I have all the relationships from this table flattened into another table, for example. if in one hierarchy of the ancestor, parent, grandson there were three records, there would be 3 records:

**ANCESTOR, DESCENDANT**
grantparent, parent
grandparent, grandchild
parent, grandchild

Instead of executing a hierarchical query to determine that the grandson is a descendant of grandparents, I can simply check for an entry (grandparent, grandchild)in this flattened table.

My question is that using this flattened table, I can most efficiently return all records that are between two nodes. Using an example, with grandparentand grandchildas my parameters, how can I return a record (grandparent, parent).

I do not want to use a hierarchical query to solve this problem ... I wonder if it is possible to do this without any connections.

Answer 1

SELECT  *
FROM    mytable
WHERE   descendant = @descendant
        AND hops < 
        (
        SELECT  hops
        FROM    mytable
        WHERE   descendant = @descendant
                AND ancestor = @ancestor
        )

This will automatically take care of cases when @ancestorit is not an ancestor @descendant.

Create an index on (descendant, hops)to make it work fast.

Answer 2

Try:

select h1.descendant intermediate_node
from hierarchy h0 
join hierarchy h1 
  on h0.ancestor = h1.ancestor 
 and h0.hops > h1.hops  -- redundant condition, but may improve performance
join hierarchy h2
  on h1.ancestor = h2.ancestor 
 and h0.descendant = h2.descendant
where h0.ancestor = :ancestor and h0.descendant = :descendant

Answer 3

SELECT
   distinct ancestor 
FROM 
   hierarchy 
WHERE descendant = :1 AND 
      ancestor IN (
                    SELECT 
                       distinct descendant 
                    FROM 
                       hierarchy WHERE ancestor = :2
                  )