0

There is such an Oracle table:

+----+-----+--------+
| ID | PID |  NAME  |
+----+-----+--------+
|  1 |     | testX  |
|  2 |     | test2  |
|  3 |   2 | test3  |
|  4 |   3 | testX  |
|  5 |   3 | test5  |
|  6 |   3 | test6  |
|  7 |   4 | test7  |
|  8 |   5 | test8  |
|  9 |   3 | test9  |
| 10 |   4 | test10 |
| 11 |   5 | testX  |
| 12 |   5 | test12 |
+----+-----+--------+

, where pid is the id of the parent record.

Need to output all records that match the condition, as well as their parent records up to the root record. Such parent records should not be duplicated with those parent records that are found during the search phase.

For example, under this condition where name = 'testX', should get this result:

+----+-----+-------+
| ID | PID | NAME  |
+----+-----+-------+
|  1 |     | testX |
|  2 |     | test2 |
|  3 |   2 | test3 |
|  4 |   3 | testX |
|  5 |   3 | test5 |
| 11 |   5 | testX |
+----+-----+-------+

How to do it?

P.S. Oracle 11.2.0.4.0.

archjkee
  • 41
  • 4
  • Thanks for the answers. But what if there are several conditions, and also if a correct order of output is required within the tree. – archjkee May 15 '18 at 14:32
  • It's hard to give you an answer without knowing the requirements. My answer below was based only on the information you provided. I'm also not sure what you mean by "correct order of output is required within the tree" – Patrick H May 15 '18 at 14:50
  • For example, if there are fields `Code1(varchar2)` and `Code2(number)` in the table, and it is needed to search by condition `where name = 'test10' and code1 = 'test1' and code2 = 5`. By the phrase "correct order of output is required within the tree" I mean that I need to apply `order siblings by` to the query, for example `order siblings by name, code2, code1`. – archjkee May 15 '18 at 16:22
  • I can do this (desired `order siblings by`) based on shrek's answer, but so far I have managed to implement this only using 2 accesses to the table, because it looks like `order siblings by` works as I need only in the case of `start with pid is null`. – archjkee May 15 '18 at 16:33

2 Answers2

1

I'm sure there is a more elegant way to do this, but this is what I came up with.

This is the with clause to generate the sample data:

with testdata as
(select 1 ID,   null PID, 'testX' NAME from dual union all
select 2 ,      null,     'test2'      from dual union all
select 3 ,      2,        'test3'      from dual union all
select 4 ,      3,        'testX'      from dual union all
select 5 ,      3,        'test5'      from dual union all
select 6 ,      3,        'test6'      from dual union all
select 7 ,      4,        'test7'      from dual union all
select 8 ,      5,        'test8'      from dual union all
select 9 ,      3,        'test9'      from dual union all
select 10,      4,        'test10'     from dual union all
select 11,      5,        'testX'      from dual union all
select 12,      5,        'test12'     from dual)

Here is the query:

select distinct id, pid, name
from(
select sys_connect_by_path(name,'/') path,
       id, pid, name
from testdata
connect by prior PID = ID)
where instr(path,'/testX') > 0
order by id

I used SYS_CONNECT_BY_PATH in order to get the name field from all parents. Then I just checked that testX was one of the elements in the string using instr.

My results are:

ID      PID     NAME
1               testX
2               test2
3       2       test3
4       3       testX
5       3       test5
11      5       testX
Patrick H
  • 653
  • 6
  • 14
0

This simple query should help - 

SELECT distinct id, pid, name FROM tab1
connect by prior pid = id
start with name = 'testX'
order by id
;

http://sqlfiddle.com/#!4/8da121/6/0

Output - 

ID  PID     NAME
1   (null)  testX
2   (null)  test2
3   2       test3
4   3       testX
5   3       test5
11  5       testX
shrek
  • 887
  • 6
  • 12