0

I've 2 tables IndexTab, ChildNodes. Index Tab has a coljumn called "Index" which gives unique value

Index

150
160
170

These values are truncated and randomly populated each execution. This table is related to below child table

ChildNodes

+----+------------+--------+
| ID | ChildValue | Parent |
+----+------------+--------+
|  1 | Child 1    |    150 |
|  2 | Child 2    |      1 |
|  3 | Child 3    |      1 |
|  4 | Child 4    |      2 |
|  5 | Child 5    |      2 |
|  6 | Child 6    |      3 |
|  7 | Child 7    |    160 |
|  8 | Child 8    |      7 |
|  9 | Child 9    |      7 |
| 10 | Child 10   |      8 |
| 11 | Child 11   |      9 |
| 12 | Child 12   |      9 |
+----+------------+--------+

Now I want the hierarchical query which is something can be written with Correlation. below is the sample which I want to write...

SELECT ChildValue, ID, Parent
FROM ChildNodes
START WITH ID = IndexTab.Index
CONNECT BY PRIOR ID = Parent
Cœur
  • 37,241
  • 25
  • 195
  • 267
user1089783
  • 63
  • 2
  • 8
  • So what do you need to generate? The hierarchy, starting with parents in the first table (and perhaps the first "table" is in fact an input of some kind)? Perhaps `start with parent IN (select index from indextab)`? –  Jan 17 '17 at 18:20

1 Answers1

0

It's not clear what do you want to get.

My assumption is you need something like:

SELECT ChildValue, ID, Parent
FROM ChildNodes
START WITH Parent IN (select Index from IndexTab)
CONNECT BY PRIOR ID = Parent
Michael Piankov
  • 1,989
  • 1
  • 8
  • 19
  • ID = (select statement) requires that what's on the right-hand side produces a single value. Does that match the OP's sample data? –  Jan 17 '17 at 18:18
  • Thanks Mike... I want to write a hierarchical query which gives me the Hierarchy values. But I can't start with a static value. I want to retrieve all the Hierarchies for all the Index values i.e. 150, 160, 170 with 1 query run. – user1089783 Jan 17 '17 at 18:20
  • Take a second look at `start with` **what** `in (...)`, too. –  Jan 17 '17 at 18:21
  • Thank @mathguy. Shame on me – Michael Piankov Jan 17 '17 at 18:23
  • Ain't no shame in wanting to help others, and in learning! Don't think I don't make mistakes myself - I make big ones, and often. And I learn from them. Cheers! –  Jan 17 '17 at 18:27