sql - 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