Simple recursive query in Oracle

You can use connect by clause.

In your case, SQL might look like:

select child, parent, levelfrom family_tree connect by prior parent = child

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 pathFROM   family_treeSTART 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

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_treeCONNECT 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_treeCONNECT BY PRIOR parent = childSTART 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.)