How to create a cascading query finding parent/child rows from table

Question

I hoping someone can help with what seems like a simple query, but seems to be harder than I anticipated. I'm looking to find the parent rows for the child row based on the corresponding ID.

TABLE1
------------------------------
ID LEVEL VALUE PARENTID
------------------------------
1 COUNTRY UK NULL
2 COUNTRY FRA NULL
3 CITY LONDON 1
4 CITY PARIS 2
5 RIVER SEINE 4
6 RIVER THAMES 3

.

SELECT VALUE FROM TABLE1 WHERE VALUE = 'THAMES'
...ID = PARENTID
...ETC
;

So for example with the above table I can pass in 'THAMES' and it will find the parent and parent's parent and so on, until it gets to a NULL value. for example:

THAMES
LONDON
UK

Thanks in advance.

• Joe W: What is the expected output?
• KS1: Sorry thought it was there, so if you put THEMES, you get THEMES > LONDON > UK.

Answer 1

with table1 as 
(
 select 1 as id, 'COUNTRY' as lvl, 'UK' as value, null as parentid from dual union all
 select 2 as id, 'COUNTRY' as lvl, 'FRA' as value, null as parentid from dual union all
 select 3 as id, 'CITY' as lvl, 'LONDON' as value, 1 as parentid from dual union all
 select 4 as id, 'CITY' as lvl, 'PARIS' as value, 2 as parentid from dual union all
 select 5 as id, 'RIVER' as lvl, 'SEINE' as value, 4 as parentid from dual union all
 select 6 as id, 'RIVER' as lvl, 'THAMES' as value, 3 as parentid from dual
)
select value from table1
connect by id = prior parentid
start with value = 'THAMES';
VALUE 
------
THAMES
LONDON
UK

Answer 2

What I did to answer this was the following (see the fiddle here):

Create and populate the table:

CREATE TABLE tab
(
 t_id INT NOT NULL PRIMARY KEY,
 t_lev VARCHAR (20) NOT NULL,
 value VARCHAR (10) NOT NULL,
 parent_id INT
);
INSERT INTO tab VALUES (1, 'COUNTRY', 'UK', NULL);
INSERT INTO tab VALUES (2, 'COUNTRY', 'FRA', NULL);
INSERT INTO tab VALUES (3, 'CITY', 'LONDON', 1);
INSERT INTO tab VALUES (4, 'CITY', 'PARIS', 2);
INSERT INTO tab VALUES (5, 'RIVER', 'SEINE', 4);
INSERT INTO tab VALUES (6, 'RIVER', 'THAMES', 3);

And then run this query (I've left in some fields which aren't necessary - just to show my (the) thinking):

WITH t1 (t_id, parent_id, lvl, root_id, path, value) AS
(
 SELECT 
 t.t_id, t.parent_id, 1 AS lvl, t.t_id AS root_id, 
 TO_CHAR(t.t_id) AS path, t.value
 FROM tab t
 WHERE t.parent_id IS NULL
 UNION ALL
 SELECT 
 t2.t_id, t2.parent_id, lvl + 1, t1.root_id, 
 t1.path || '-> ' || t2.t_id AS path, t1.value || '-> ' || t2.value AS pth
 FROM tab t2, t1
 WHERE t2.parent_id = t1.t_id
)
SELECT * FROM t1;

Result:

T_ID PARENT_ID LVL ROOT_ID PATH VALUE
 1 NULL 1 1 1 UK
 2 NULL 1 2 2 FRA
 3 1 2 1 1-> 3 UK-> LONDON
 4 2 2 2 2-> 4 FRA-> PARIS
 5 4 3 2 2-> 4-> 5 FRA-> PARIS-> SEINE
 6 3 3 1 1-> 3-> 6 UK-> LONDON-> THAMES

With thanks to the ever-excellent Tim Hall and his oracle-base site - a wonder to behold!

To pick out the 'THAMES', just add

SELECT * FROM t1
WHERE INSTR(value, 'THAMES') != 0;

Fiddle here.