CODEWITHSUNDEEP
oracle-databasehierarchyhierarchical-data
nkuhta
nkuhta

Reputation: 11128

Select tree by leaf in Oracle Database

There is hierarchical queries in Oracle, using CONNECT BY PRIOR. Everybody knows, how to select children by parent, but I need to select parent by child.

Here is my table:

ID  PID     NAME            TYPE
1   null    EARTH           PLANET
2   1       USA             COUNTRY
3   2       CALIFORNIA      STATE
4   3       Los_Angeles     CITY
5   3       San_Francisco   CITY
6   3       San_Diego       CITY

In my app I have ID of San_Diego, and I need to know, in what country San_Diego is? I need to get USA (TYPE=COUNTRY) with my query? How to select it with oracle hierarchical structures?

Upvotes: 1

Views: 3542

Answers (4)

Pratsam
Pratsam

Reputation: 162

Hierarchy means, that one row of a table is a "parent", and another one - a "child". PRIOR is used to show who is who. Clause CONNECT BY PRIOR EMPNO = MGR means, that if two rows have the same value, but one row in a column EMPNO, and the second - in a column MGR, then second is a "parent" and first is a "child". So, query 

SELECT EMPNO,ENAME,MGR,LEVEL
FROM TMP_PCH
CONNECT BY PRIOR EMPNO = MGR
START WITH MGR = 'John'

returns all subordinates of John (and John itself), and query 

SELECT EMPNO,ENAME,MGR,LEVEL
FROM TMP_PCH
CONNECT BY PRIOR MGR = EMPNO
START WITH MGR = 'John'

returns all bosses of John (and John itself).

Upvotes: 5

Drumbeg
Drumbeg

Reputation: 1934

SELECT * FROM places_table
WHERE type = 'COUNTRY'
START WITH id = 6 -- San Diego
CONNECT BY PRIOR pid = id;

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

You need to create correct condition for connect by and start with leaf 'San Diego':

select name 
  from (
    select * from test 
      connect by id = prior pid
      start with name='San_Diego')
  where type='COUNTRY'

SQL Fiddle demo

Upvotes: 1

pablomatico
pablomatico

Reputation: 2242

There's no difference, you just have to use PRIOR in the parent side. For example to get the tree for the city of San Diego:

WITH your_table AS (  
  SELECT 1 id, NULL pid, 'EARTH' name, 'PLANET' type FROM dual
  UNION
  SELECT 2 id, 1 pid, 'USA' name, 'COUNTRY' type FROM dual
  UNION
  SELECT 3 id, 2 pid, 'CALIFORNIA' name, 'STATE' type FROM dual
  UNION 
  SELECT 4 id, 3 pid, 'Los_Angeles' name, 'CITY' type FROM dual
  UNION 
  SELECT 5 id, 3 pid, 'San_Francisco' name, 'CITY' type FROM dual
  UNION
  SELECT 6 id, 3 pid, 'San_Diego' name, 'CITY' type FROM dual
)
SELECT id, name, level
FROM your_table
CONNECT BY id = PRIOR pid
START WITH id = 6

If you just wanted to get the country, you could filter by level:

WITH your_table AS (  
  SELECT 1 id, NULL pid, 'EARTH' name, 'PLANET' type FROM dual
  UNION
  SELECT 2 id, 1 pid, 'USA' name, 'COUNTRY' type FROM dual
  UNION
  SELECT 3 id, 2 pid, 'CALIFORNIA' name, 'STATE' type FROM dual
  UNION 
  SELECT 4 id, 3 pid, 'Los_Angeles' name, 'CITY' type FROM dual
  UNION 
  SELECT 5 id, 3 pid, 'San_Francisco' name, 'CITY' type FROM dual
  UNION
  SELECT 6 id, 3 pid, 'San_Diego' name, 'CITY' type FROM dual
)
SELECT id, name, level
FROM your_table
WHERE LEVEL = 3
CONNECT BY id = PRIOR pid
START WITH id = 6

Upvotes: -1

Related Questions