Databases
StackOverflow2013
Postsdbo
POCTE, recursive search in 2 different tables
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCTE, recursive search in 2 different tables
    text
    copied!<p>i have 2 queries go gather data from different tables, both recursive, but closely related.</p> <p>Both work well:</p> <p>First pulls subsidiaries:</p> <pre><code>with relations as ( select orgid as children,org_immediate_parent_orgid as orgid,'Sub' as relation from oa.organizations ) select distinct relation, level, orgid, children from relations where children is not null start with orgid in (identifier) connect by nocycle prior children = orgid order by 2,3,4 </code></pre> <p>Second affiliates:</p> <pre><code>with relations as ( select affiliated_orgid as children, orgid as orgid,'Aff' as relation from oa.org_affiliations ) select distinct relation, level, orgid, children from relations where children is not null start with orgid in (identifier) connect by nocycle prior children = orgid order by 2,3,4 </code></pre> <p>Recursion goes well in both, as i expected. Yes children and orgid are exchanged in queries, i would say subsidiaries is a "inverse relation" of affiliation (atleast in concept), but results are as expected</p> <p>I want to run all together. Now i want the results of the first loop(for subs and aff) as input in the following loop(for subs and aff), till children are null. So if i get one result from each, i want both ids as input for each query.</p> <p>I know i cant do inside "With As" the following.</p> <pre><code>select orgid as children,org_immediate_parent_orgid as orgid,'Sub' as relation from oa.organizations UNION ALL select affiliated_orgid as children, orgid as orgid,'Aff' as relation from oa.org_affiliations </code></pre> <p>What are my alternatives, solutions?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload