Simple recursive query in Oracle

sql oracle recursive-query
ac_nook picture ac_nook · May 1, 2018 · Viewed 15.5k times · Source

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

mkuligowski picture mkuligowski · May 1, 2018

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

Related questions

What is the equivalent PostgreSQL syntax to Oracle's CONNECT BY ... START WITH?

In Oracle, if I have a table defined as … CREATE TABLE taxonomy ( key NUMBER(11) NOT NULL CONSTRAINT taxPkey PRIMARY KEY, value VARCHAR2(255), taxHier NUMBER(11) ); ALTER TABLE taxonomy ADD CONSTRAINT taxTaxFkey FOREIGN KEY (taxHier) REFERENCES tax(key); With these values … key …

Read More
Oracle Connect By Prior for Recursive Query Syntax

Suppose I had the following table in my oracle DB: ID: Name: Parent_ID: 123 a 234 345 b 123 234 c 234 456 d 345 567 e 567 678 f 567 And what I would like to do is find, for each ID the ULTIMATE parent ID (described as row, that …

Read More
Oracle SQL Analytic query - recursive spreadsheet-like running total

I have the following data, composed of the A value, ordered by MM (month). The B column is computed as GREATEST(current value of A + previous value of B, 0) in a spreadsheet-like fashion. How can I compute B using a …

Read More