Oracle sql query to get parent node based on type?

Question

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.

I don't understand. What do you mean by "get the DEP"? Do you mean, stop when type = 'DEP'? Do you then need to get the name of the DEP? What if, going up the tree, you never hit DEP? (Or are you guaranteeing that you always will? Well, obviously that is not possible; if you "start with 1", you will never hit DEP.) — user5683823
– user5683823, Commented Jul 24, 2018 at 2:47
@mathguy Yes. I need to get the name/id of the parent having type of "DEP". And yes it's guaranteed that DEP will always be there. Not necessarily the next level up but it will be up there. Start will always be a level below DEP. — ads
– ads, Commented Jul 24, 2018 at 5:33

Michael Piankov · Accepted Answer · 2018-07-24 02:38:18Z

1

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

answered Jul 24, 2018 at 2:38

Michael Piankov

1,9971 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ads Over a year ago

Thanks. To get the DEP, I added in the where clause "type='DEP'". It seems to work but I wanted to check with you if this is the correct way.

user5683823 · Accepted Answer · 2018-07-24 05:43:15Z

0

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'.

answered Jul 24, 2018 at 5:43

user5683823

Comments

tungns · Accepted Answer · 2018-07-24 02:56:00Z

0

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;

edited Jul 24, 2018 at 2:56

answered Jul 24, 2018 at 2:46

tungns

961 silver badge9 bronze badges

Collectives™ on Stack Overflow

Oracle sql query to get parent node based on type?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related