CODEWITHSUNDEEP
oracle-databasehierarchical
user3338737
user3338737

Reputation: 21

Hierarchical query - get all parents along a specific path

I am on Oracle 11g.

I have the following table structure:

Parent      Child
------------------
C01          165

C01          175

C01          C02

C01          C03

C02          C05

C03          C05

C05          10

Now I want to find all parents, of lets say the number 10 until the root. There are 2 possible paths.

10->C05->C02->C01

10->C05->C03->C01

Is it possible to specify which path to go up? For example, I would want only the path with C02 in it.

Is it also possible to stop at a particular point? For example, go up the C02 path and stop when you reach C02?

I am lost about how to even approach this. Any help will be appreciated.

Upvotes: 2

Views: 7932

Answers (1)

Kombajn zbożowy
Kombajn zbożowy

Reputation: 10693

Now I want to find all parents, of lets say the number 10 until the root.

SELECT path FROM (
  SELECT item, parent, sys_connect_by_path(item, '/') path
  FROM tree
  CONNECT BY PRIOR item = parent
  START WITH parent IS NULL
)
WHERE item = '10';

Is it possible to specify which path to go up? For example, I would want only the path with C02 in it.

SELECT path FROM (
  SELECT item, parent, sys_connect_by_path(item, '/') path
  FROM tree
  CONNECT BY PRIOR item = parent
  START WITH parent IS NULL
)
WHERE item = '10'
AND path LIKE '%/C02/%';

Is it also possible to stop at a particular point? For example, go up the C02 path and stop when you reach C02?

SELECT path FROM (
  SELECT item, parent, sys_connect_by_path(item, '/') path
  FROM tree
  CONNECT BY PRIOR item = parent
  START WITH item = 'C02'
)
WHERE item = '10';

-> SQLFiddle

Upvotes: 2

Related Questions