CODEWITHSUNDEEP
oracle-databasehierarchyconnect-by
ads
ads

Reputation: 1723

Oracle sql query to get parent node based on type?

I need help in building a hierarchical query to get parent node based on type. For example:

Table (org)

pid|cid|type|name
   |  1|MGT |Ofc Pres
  1|  2|DEP |HR
  1|  3|DEP |IT
  3|  4|DIV |Web
  3|  5|DIV |Database
  4|  6|SEC |HTML
  4|  7|SEC |JAVA

My goal is to get the DEP given the cid (6 or 7). Using the query below, I only get the pid (4).

  select pid
    from org
   start
    with cid = 7
 connect
      by
   prior cid = pid

Appreciate any feedback.

UPDATE 07/24/2018:

Some additional info that might help.

MGT (Management) is the highest/root level DEP (Department) is under MGT. DIV (Division) is under DEP. SEC (Section) is under DIV.

So if given a child id of type SEC, I need to get the DEP (Department) which it is under (which means I need to get the DIV first then the DEP). If given a child id of type DIV, then I need to get DEP which it is under.

Upvotes: 0

Views: 1287

Answers (3)

user5683823
user5683823

Reputation:

One issue has already been pointed out: connect by prior pid = cid. To stop when the type is DEP, you need to add one more condition to connect by:

connect by prior pid = cid and prior type != 'DEP'

and add a `WHERE clause:

where type = 'DEP'

(note that the WHERE clause comes before START WITH and CONNECT BY).

It is not clear what you want to SELECT, but that should be easy.

Actually, with the simple structure you have, the WHERE clause would suffice. Not having the additional condition in CONNECT BY means that the query will do just a little more work than necessary; it will find the MGT row as well, but it will still select the unique row where type = 'DEP'.

Upvotes: 0

tungns
tungns

Reputation: 96

Based on Oracle document, PRIOR operator refer to the parent row.

So your query should be

  select pid
    from org
   where level = 2 --to get DEP
 connect by prior pid = cid;

Upvotes: 0

Michael Piankov
Michael Piankov

Reputation: 1997

You need to build your tree from leaf to root.

So change pid and cid: 

 select pid
    from org
   start
    with cid = 7
 connect by prior pid = cid

Upvotes: 1

Related Questions