SQL Multi Condition CTE Recursion

Question

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.

Something along the lines, 2 tables(ignoring some unique identifiers):

organizations

orgid | org_immediate_parent_orgid
1     | 2
2     | 2
3     | 1
5     | 4

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

org_affiliations

orgid | affiliated_orgid
2     | 3
2     | 5
4     | 1
1     | 5

orgid --> affiliated_orgid is Company has affiliate

The visual representation should be something like:

On red relations from organizations, on blue relations org_affiliations.

If Want to get all companies owned by 2(or subsidiary son of 2) has some part in it them:

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

returns

org_immediate_parent_orgid| orgid
1                         | 2
2                         | 2
3                         | 1

If Want to get all companies were 2(or affiliated son of 2) has some part in it them:

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

returns

orgid | affiliated_orgid
2     | 3
2     | 5

So of all possible relations:

• Aff --> Aff
• Aff --> Sub
• Sub --> Aff
• Sub --> Sub

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.

How can i pull all possible relations in one single recursive query?

If i pass identifier 2 it should be possible the following return:

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

In each cycle would check subs and affiliates for each identifier. Repeat for the new children.

Any idea on how to approach it?

TL:DR: 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.

Edit: As commented by Craig, I fixed the output.

Edit2: Following on the good help Craig and Bob Jarvis gave i continue to run into problems.

For gathering subsidiaries, the following code works flawlessy, and the output is as i would like:

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

Same for AFF:

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

but cant have "union all"?

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

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?

Bob Jarvis solution wont work in cases where i have comp-sub-sub-aff, or it finds all subsidiaries of company or all affiliates

Answer 1

Moving this from a comment to an actual answer and providing what I believe you need.

A couple things.. one is minor.. I believe you have the labels of your first connect by returns output backwards. Also, I don't get how you get the last two rows in your final output. 4 is a parent of 5, not a child, so why does it show up? And if it isn't there, then the last line won't be as well.

If I am reading it correctly, you can use something like:

with
relations as
(
    select
        orgid,
        org_immediate_parent_orgid parent_id,
        'Sub' relation
    from
        organizations
    union all
    select
        orgid,
        null parent_id,
        'Aff' relation
    from
        org_affiliations
    where
        orgid not in (
            select affiliated_orgid
            from org_affiliations
        )
    union all
    select
        affiliated_orgid orgid,
        orgid parent_id,
        'Aff' relation
    from
        org_affiliations
)
select distinct relation, level, parent_id, orgid
from relations
where parent_id is not null
start with orgid = 2
connect by
    nocycle prior orgid = parent_id
order by 2,3,4

Which gives the following output:

RELATION|LEVEL|PARENT_ID|ORGID
Sub     |1    |2        |2
Sub     |2    |2        |1
Aff     |2    |2        |3
Aff     |2    |2        |5
Sub     |3    |1        |3
Aff     |3    |1        |5

The biggest thing is that the 2 tables were set up opposite of each other (organizations had a link to the parent, affiliations had a link to the child). So I am making them into the same format in the WITH clause, and then using the connect by on the combined set.

Also, for some reason Oracle gives the first loop a different level than the others since it is a self reference. I am assuming that if this is a problem, you can put in some custom logic for this case.

Answer 2

Here's a start at it:

select 'SUB -> SUB' AS TYPE,
       m.orgid AS ORGID,
       m.org_immediate_parent_orgid AS PARENT_OR_AFF
  from organizations m
  where m.org_immediate_parent_orgid is not NULL
  start with m.orgid in (2)
  connect by nocycle prior m.orgid = m.org_immediate_parent_orgid
UNION ALL
select 'AFF -> AFF' AS TYPE,
       aff.orgid AS ORGID,
       aff.affiliated_orgid AS PARENT_OR_AFF
  from org_affiliations aff
  where aff.affiliated_orgid is not NULL
  start with aff.orgid IN (2)
  connect by nocycle prior aff.affiliated_orgid = aff.orgid;

If you add the subqueries to get the remaining relationships you should be good to go.

Share and enjoy.