11

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:

Database representation

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

blueomega
  • 281
  • 2
  • 15
  • 2
    +1 for shear beauty. however, too long... did not listen :) – Bohemian Aug 05 '11 at 10:29
  • 1
    Its too long for me to read, understand and reply! – Kangkan Aug 05 '11 at 10:55
  • 2
    **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. – blueomega Aug 05 '11 at 12:02
  • @blueomega - Did you ever figure this out, or are you still having issues? If it still isn't working I will take another look. – Craig Aug 18 '11 at 19:15

3 Answers3

2

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.

Craig
  • 5,740
  • 21
  • 30
  • Hi Craig, the label exchange is not problematic, the system that eats this data doesn't care. Ups 4 should be subsidiary of 5, not the other way around, excellent catch. All in all, excellent work, past my bedtime, but ill test it later and provide more feedback. Any change should be easy now :) – blueomega Aug 05 '11 at 21:24
  • Seems i still run into problems, explained above. the part of the query with "null parent_id", if i understand it correctly, i dont need the output ids of companies with no more aff – blueomega Aug 08 '11 at 11:18
1

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.

0

Untested. Create a view to simplify pulling data from your 2 tables first.

create view related(orgid, relatedid) as 
    select orgid, org_immediate_parent_orgid as relatedid from organizations
    union distinct
    select orgid, affiliated_orgid as relatedid from affiliated;

Now we can use that to iteratively find all interesting orgids easily.

with recursive related_recursive(orgid, relatedid) as (
    select orgid, relatedid from related where relatedid = 2
    union
    select r.origid, rr.relatedid from related_recursive rr, related r
        where rr.orig = r.relatedid
) 
select orgid from related_recursive;

You could even remove the relatedid column of related_recursive in this case, but it is useful and necessary if you want to remove or change the where part and select * from related_recursive.

Just remember, CTEs are evaluated fully before the main query so this might pull in many pages before eventual filtering in the main query.

Jürgen Strobel
  • 2,200
  • 18
  • 30
  • I didn't see you used connect by, which I think is oracle specific? It should be no problem to transform this from standard "with recursive" if oracle doesn't support it yet. – Jürgen Strobel Aug 15 '11 at 13:10