Oracle-SQL Single select, cascade by field

Question

I'm not entirely sure how to phrase what I mean. Let me try: Is there a way to select all elements that cascade by a reference field?

For instance, I have the rows:

parentRef   | Reference | Data
------------------------------
aContainer  | mainObj   | "Parent"
mainObj     | secondObj | "Child 1"
secondObj   | thirdObj  | "Child 2"
nonExistent | blankObj  | "Don't select me!"

And I want to select mainObj, secondObj, thirdObj in a single statement when I only know one parentRef: "aContainer". Is this possible?

I could do this by having my code perform many queries: select...where parentRef = 'aContainer', then select...where parentRef = 'mainObj', etc, but I really don't want to hammer my DB with many queries, primarily for speed.

Edit: Tree Queries! That's the search term I needed.

score 2 · Accepted Answer · answered Jun 02 '16 at 18:33

2

Oracle can do Tree Queries, have a look at START WITH and CONNECT BY

answered Jun 02 '16 at 18:33

Turo

4,724
2
14
27

That's what I needed exactly! `start with parentRef = 'aContainer' connect by prior Reference = parentRef` – David Crane Jun 02 '16 at 18:47

score 0 · Answer 2 · answered Jun 02 '16 at 18:36

0

If I understand you right, you want something like a correlated query. This will allow you to get only items that have a parent reference in the table. an example would look something like the following (although I might have reversed the logic):

select 
parentRef
, Reference
, Data 
from mytable parentTable 
where Reference in (
select
 reference 
 from mytable childTable 
 where childTable.reference = parenttable.parentref)

answered Jun 02 '16 at 18:36

Zoldren

66
3

Indeed that would work, until I decide to add an item with Reference='nonExistent'. For this example that name doesn't make sense, but it could be anything else. START WITH and CONNECT BY were what I needed exactly, though! – David Crane Jun 02 '16 at 18:45

Oracle-SQL Single select, cascade by field

2 Answers2