CODEWITHSUNDEEP
sqloracle-databaserecursive-query
bidi
bidi

Reputation: 150

Oracle Hierarchical Query - Get records in hierarchy where children are in a list

I have a table with Parent, Child relationship. On another table I have a list of members that are leaves in the first table.

What I would like to do is to have the first table filtered by the members which are parents of the member in the second table, or the members of the second table. I hope my problem is clear, if not please ask.

Database is Oracle 10g.

Upvotes: 0

Views: 545

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Let's say you have this hierarchy in table TH and in members table TM there are three rows 'GC Carl', 'PT Mike', 'PT Mary' (marked green).

enter image description here

To find all their parents, grandparents etc. (marked yellow) you need this simple hierarchical query:

select distinct id
  from th
  connect by id = prior pid
  start with id in (select id from tm)

SQLFiddle demo

If you are looking only for parents and you need to do in hierarchical way then: 

select distinct id
  from th
  connect by id = prior pid and level <= 2
  start with id in (select id from tm)

(Martin is eliminated, because he is not parent for any member and he is absent in TM).

If this is not what you wanted you should definitely precise your query, add sample input data and desired output.

Upvotes: 2

Related Questions