1

I have two tables. One is for Task and second is dependency table for the tasks. I want a query to give me all the tasks (recursively) based on a particular id.

I have two tables. One is for Task

ID  TASK  
1   Abc  
2   Def  
3   Ghi  
4   Jkl  
5   Mno  
6   Pqr

The second one is for getting dependent tasks 

ID  DEPENDENT_ON  
2   1  
3   1  
4   2  
4   6  
5   2  
6   5

Is it possible to write a sql query to get a list of all the tasks (recursive) which are dependent on a particular task.

Example.
I want to check all tasks dependent on ID=1. Expected output (which is 2 and 3): 

2.Def
3.Ghi

Furthermore query should also give output of these two dependent tasks and so on. Final output should be: 

2.Def -- level one
3.Ghi -- level one 
4.Jkl -- Dependent on task 2  
5.Mno -- Dependent on task 2 
6.Pqr -- Dependent on task 5

Formatting is not important. Just output is required

APC
  • 144,005
  • 19
  • 170
  • 281
Kash
  • 15
  • 4
  • Possible duplicate of [Simple recursive query in Oracle](https://stackoverflow.com/questions/50123407/simple-recursive-query-in-oracle) – Jeroen Heier Apr 06 '19 at 07:32
  • As explained I need to join two tables and then do a recursive search. I am unable to do the same. – Kash Apr 06 '19 at 07:41

1 Answers1

0

I need to join two tables and then do a recursive search.

You must OUTER JOIN the second table (which you didn't name, so I have called it TASK_TREE) through DEPENDENT_ON to the parent ID. Outer join because task 1 is the top of the tree and depends on no task. Then use Oracle's hierarchical query syntax to walk the tree:

select t.id, t.task, tt.dependent_on, level
from tasks t
     left outer join task_tree tt on tt.id = t.id
connect by prior t.id = tt.dependent_on 
start with t.id = 1
/

I have included the level so you can see how the tree unfurls. The Oracle SQL documentation covers hierarchical queries in depth. Find out more. If you don't want to use Oracle's proprietary hierarchical syntax, from 11gR2 Oracle supported recursive WITH clause. Find out more.

Incidentally, your posted data contains a error. Task 4 depends on both 2 and 6. Hierarchies must have child nodes which depend on a single parent node. Otherwise you'll get all sorts of weird results.

APC
  • 144,005
  • 19
  • 170
  • 281
  • Thanks for this, but data doesn't contain error, single task might be dependent on one or more task. This is because data required by 4 is populated by 2 and 6. – Kash Apr 06 '19 at 08:29
  • Then, as I say, hierarchical queries will contain odd results. Task 4 will appear multiple times in the result set. – APC Apr 06 '19 at 09:18