Databases
StackOverflow2013
Postsdbo
POSQL Multi Condition CTE Recursion
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Multi Condition CTE Recursion
    primarykey
    data
    text
    <p>I the database i have the 2 following pieces of information for each identifier. The company that controls them, and companies where they have small bits of control.</p> <p>Something along the lines, 2 tables(ignoring some unique identifiers):</p> <p><strong>organizations</strong></p> <pre><code>orgid | org_immediate_parent_orgid 1 | 2 2 | 2 3 | 1 5 | 4 </code></pre> <p>The relation orgid --> org_immediate_parent_orgid means company has parent. Por me its relevant only org_immediate_parent_orgid --> orgid the parent of the companies has as subsidiary</p> <p><strong>org_affiliations</strong></p> <pre><code>orgid | affiliated_orgid 2 | 3 2 | 5 4 | 1 1 | 5 </code></pre> <p>orgid --> affiliated_orgid is Company has affiliate</p> <p>The visual representation should be something like:</p> <p><img src="https://i.imgur.com/T3Hk3.jpg" alt="Database representation"></p> <p>On red relations from <strong>organizations</strong>, on blue relations <strong>org_affiliations</strong>.</p> <p>If Want to get all companies owned by 2(or subsidiary son of 2) has some part in it them:</p> <pre><code>select m.org_immediate_parent_orgid ,m.orgid from oa.organizations m where m.org_immediate_parent_orgid is not null start with m.orgid in (Identifiers) connect by nocycle prior m.orgid=m.org_immediate_parent_orgid </code></pre> <p>returns</p> <pre><code>org_immediate_parent_orgid| orgid 1 | 2 2 | 2 3 | 1 </code></pre> <p>If Want to get all companies were 2(or affiliated son of 2) has some part in it them:</p> <pre><code>select aff.orgid,aff.affiliated_orgid from oa.org_affiliations aff where aff.affiliated_orgid is not null start with aff.orgid in(Identifiers) connect by nocycle prior aff.affiliated_orgid =aff.orgid </code></pre> <p>returns</p> <pre><code>orgid | affiliated_orgid 2 | 3 2 | 5 </code></pre> <p>So of all possible relations:</p> <ul> <li>Aff --> Aff</li> <li>Aff --> Sub</li> <li>Sub --> Aff</li> <li>Sub --> Sub</li> </ul> <p>I only find Sub --> Sub (subsidiaries of subsidiaries), relations (2 --> 1 and relations 1 --> 3) and Aff --> Aff, relations (2 --> 3 and relations 2 --> 5). Also it requires me 2 separate queries.</p> <p>How can i pull all possible relations in one single recursive query?</p> <p>If i pass identifier 2 it should be possible the following return: </p> <pre><code>Relation | Loop| orgid | children Sub | 1 | 2 |2 Sub | 1 | 2 |1 Aff | 1 | 2 |3 Aff | 1 | 2 |5 Sub | 2 | 1 |3 Aff | 2 | 1 |5 </code></pre> <p>In each cycle would check subs and affiliates for each identifier. Repeat for the new children.</p> <p>Any idea on how to approach it?</p> <p><strong>TL:DR:</strong> 2 tables(subsidiaries\affiliates), 2 queries. want single query where from a company i find all subsidiaries and affiliates and all possible combination of subs\affs. Final expected result show, just follow the picture representation.</p> <p><strong>Edit:</strong> As commented by Craig, I fixed the output.</p> <p><strong>Edit2:</strong> Following on the good help Craig and Bob Jarvis gave i continue to run into problems.</p> <p>For gathering subsidiaries, the following code works flawlessy, and the output is as i would like:</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>Same for AFF:</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>but cant have "union all"?</p> <pre><code>with relations as ( 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 ) 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>In sql developer i went and check "explain plan and cost from each jump from 7 to 400k, just by adding "union all". Any workarround? Is the problem inside the CTE, in the union alL?</p> <p>Bob Jarvis solution wont work in cases where i have comp-sub-sub-aff, or it finds all subsidiaries of company or all affiliates</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USblueomega
    1. USblueomega
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Multi Condition CTE Recursion
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Multi Condition CTE Recursion
      1. USAPC
      1. VTFavorite
    3. VO
      singulars
      1. POSQL Multi Condition CTE Recursion
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 for shear beauty. however, too long... did not listen :)
      singulars
      1. POSQL Multi Condition CTE Recursion
      1. USBohemian
    2. COHad to explain somehow :D
      singulars
      1. POSQL Multi Condition CTE Recursion
      1. USblueomega
    3. COIts too long for me to read, understand and reply!
      singulars
      1. POSQL Multi Condition CTE Recursion
      1. USKangkan
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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