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