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.)

Answer 4

Are you familiar with the SCOTT.EMP table? It's in the "standard" SCOTT schema (which, unfortunately, is no longer pre-packaged with every copy of Oracle database, since version 12.1 or so). Check your database: you may find it there. Or ask your DBA about it.

Anyway: the table shows the 14 employees of a small business, and it includes the employee's ID as well as his or her manager's employee ID. So, suppose you start with a given employee and you want to find his or her highest-level boss. (Similar to your test problem.) In this particular hierarchy, the highest-level "ancestor" is unique, but that is irrelevant; the recursive query would work the same way if each department had a "head of department" and there was no CEO above the heads of department.

In this arrangement, it's easy to identify the "boss of all bosses" - he does not have a boss. In his row, the manager ID is null. This is a very common arrangement for the "root" (or "roots") of tree-like hierarchies.

Here is how you would find the boss, starting with a specific employee id, and using a recursive query - which is what I understand is what you are looking to practice on. (That is: if I understand correctly, you are not interested in solving the problem "by any means"; rather, you want to see how recursive queries work, in a small example so you can understand EVERYTHING that goes on.)

with
  r ( empno, mgr ) as (
    select  empno, mgr      -- ANCHOR leg of recursive query
      from  scott.emp
      where empno = 7499
    union all
    select  e.empno, e.mgr  -- RECURSIVE leg of recursive query
      from  scott.emp e inner join r on e.empno = r.mgr
  )
select empno
from   r
where  mgr is null
;

I will not try to guess where you may have difficulty understanding this example. Instead, I will wait for you to ask.