Simple recursive query in Oracle

Question

I'm currently having some trouble understanding and writing recursive queries. I understand that recursive queries are used to search through hierarchies of information, but I haven't found a simple solution online that can travel up a hierarchy. For example, let's say that I have a relation that models a family tree:

create table family_tree (
child varchar(10)
parent varchar(10)
);

If I wanted to write a recursive query that travelled up this family tree, collecting all parents until origin, how should I go about this?

Thanks in advance.

Answer 1

You can use connect by clause.

In your case, SQL might look like:

select child, parent, level
from family_tree 
connect by prior parent = child

Answer 2

If I wanted to write a recursive query that travelled up this family tree, collecting all parents until origin, how should I go about this?

Use a hierarchical query and the SYS_CONNECT_BY_PATH( column_name, delimiter ) function:

Oracle 18 Setup:

create table family_tree (
  child varchar(10),
  parent varchar(10)
);

INSERT INTO family_tree ( child, parent )
  SELECT 'B', 'A' FROM DUAL UNION ALL
  SELECT 'C', 'B' FROM DUAL UNION ALL
  SELECT 'D', 'C' FROM DUAL UNION ALL
  SELECT 'E', 'D' FROM DUAL UNION ALL
  SELECT 'F', 'C' FROM DUAL;

Query 1:

SELECT SYS_CONNECT_BY_PATH( parent, ' -> ' ) || ' -> ' || child AS path
FROM   family_tree
START WITH parent = 'A'
CONNECT BY PRIOR child = parent;

Results:

PATH
-------------------------
 -> A -> B
 -> A -> B -> C
 -> A -> B -> C -> D
 -> A -> B -> C -> D -> E
 -> A -> B -> C -> F

Answer 3

There is an ANSI syntax that I'm not really familiar with and there is an Oracle syntax that I usually use. The Oracle syntax uses a CONNECT BY ... PRIOR clause to build the tree and a START WITH clause that tells the database where to start walking the tree. It will look like this:

SELECT child, parent, level
  FROM family_tree
CONNECT BY ...
START WITH ...

The START WITH clause is easier. You're looking "up" the tree, so you'd pick a child where you want to start walking the tree. So this would look like START WITH parent = 'John'. This is our level 1 row. I'm assuming John's row will have him as the parent and no children, since it's the bottom of the tree.

Now, think about how rows in the tree relate to each other. If we're looking at a level 2 row, how do we know if it is the correct row to the "John" row? In this case, it will have John in the child column. So we want a clause of: CONNECT BY PRIOR parent = child. That means "the prior row's parent equals this row's child"

So the query looks like:

SELECT child, parent, level
  FROM family_tree
CONNECT BY PRIOR parent = child
START WITH parent = 'John'

SQL Fiddle example

(This is a bit of a strange example since actual children have two parents, but that would make it more complicated.)