[ADMIN] overcoming a recursive relationship in a sql statement

[b***@comcast.net]

Hello,


So I am not very advanced in SQL, and after a week of wild SQL experimenting have finally reduced my thought process to a complete blank on this query...


I have a list of organizations in table:organization and a one to many list of organizational partnerships in a second table (table:partners). The 'partners' table lists each organization id that belongs to each partnership.


The partnership relationships are recursive in that each partner is an 'ego' to one or more 'alter' partners, and an alter to other 'ego' partners.


So I havent been able to figure out a way to select business partners 'alter' organizations that are distinctly related to each 'ego' organization.



I would like to create SQL output that looks like this:


self partner id business partner
Org1 100 Org2
Org1 100 Org3
Org2 100 Org1
Org2 100 Org3
Org3 100 Org1
Org3 100 Org2




(The futher complexities are that every combination of partnerships and the life cycle of each partnership can be a many to many relation and each org may be partnered or opposed to other orgs, or a partnership may eventually turn sour resulting in the partnership being dissolved and the participants becoming competitors - its endless in this regard :<)


I tried a many to many tables querying ego_org and alter_org, however each alter has the same ego elsewhere in the table, so the normanization is screwed up...


so i finaly went back to a one to many model shown below:




table:org
--------------------------------
orgid org name
org1 xyz
org2 abc
org3 blah blah
orgx etc


Table: partners
---------------------------------------
partnerid member_org
100 org1
100 org2
101 org1
101 org25
102 org2
102 org3


table: affiliation unique constraints = (partner, competitor, ally)
-------------------------------------
affiliation_id affiliation
100 partner
101 competitor
102 ally
103 competitor
1xx etc




E ach organization is connected to other org(s) as a business partner or competitor. (which I ignore for the moment).


I have tried a variety of self joins, and many to many joins, to no avail.

I have no idea how to select "business partners" for each organization that do not include the ego partner.


all I have so far is this ( less all the many to many joins and self joins attempts etc.)



select p.partnum as "partner id", o.org as "self", p.member_id as "business partner",a.affiliation
from testorg o
join partners p on o.org = p.member_id
join
order by p.partnum asc, o.org




the sql returns a duplicate list for self (ego) and business partner (alter orgs):


partner id self business partner
100 Org1 Org1
100 Org2 Org2
100 Org3 Org3
101 Org1 Org1
101 Org25 Org25
102 Org2 Org2
102 Org3 Org3
103 Org4 Org4
104 Org1 Org1
104 Org16 Org16

I have gotten various Cartesian joins showing every org related to every other, but have not been unable to properly filter the 'partner organizations column to exclude 'ego' e.g. the organization that "others" are related to.


Can anybody offer a solution that selects only the partners that are linked to each organization?
thanks very much,


glenn B

[Craig James]

Post by b***@comcast.net
Hello,
So I am not very advanced in SQL, and after a week of wild SQL
experimenting have finally reduced my thought process to a complete blank
on this query...
I have a list of organizations in table:organization and a one to many
list of organizational partnerships in a second table (table:partners).
The 'partners' table lists each organization id that belongs to each
partnership.
The partnership relationships are recursive in that each partner is an
'ego' to one or more 'alter' partners, and an alter to other 'ego' partners.
So I havent been able to figure out a way to select business partners
'alter' organizations that are distinctly related to each 'ego'
organization.

I'm not positive from your description, but it seems like the concept you
are missing is that a single table can have several aliases in one SQL
query, which makes it appear to be several different tables. Take a simple
example: a "personnel" table with employees and supervisors. Find
everyone with supervisor "Jones":

select name from personnel emp join personnel super on (emp.supervisor =
super.name)
where super.name = 'Jones';

Even though it's one table, you can make it look like two tables.

Craig

Post by b***@comcast.net
selfpartner idbusiness partnerOrg1100Org2Org1100Org3Org2100Org1Org2100Org3
Org3100Org1Org3100Org2
(The futher complexities are that every combination of partnerships and
the life cycle of each partnership can be a many to many relation and each
org may be partnered or opposed to other orgs, or a partnership may
eventually turn sour resulting in the partnership being dissolved and the
participants becoming competitors - its endless in this regard :<)
I tried a many to many tables querying ego_org and alter_org, however each
alter has the same ego elsewhere in the table, so the normanization is
screwed up...
table:org
--------------------------------
orgid org name
org1 xyz
org2 abc
org3 blah blah
orgx etc
Table: partners
---------------------------------------
partnerid member_org
100 org1
100 org2
101 org1
101 org25
102 org2
102 org3
table: affiliation unique constraints = (partner, competitor, ally)
-------------------------------------
affiliation_id affiliation
100 partner
101 competitor
102 ally
103 competitor
1xx etc
Each organization is connected to other org(s) as a business partner or
competitor. (which I ignore for the moment).
I have tried a variety of self joins, and many to many joins, to no avail.
I have no idea how to select "business partners" for each organization
that do not include the ego partner.
all I have so far is this ( less all the many to many joins and self joins attempts etc.)
select p.partnum as "partner id", o.org as "self", p.member_id as
"business partner",a.affiliation
from testorg o
join partners p on o.org = p.member_id
join
order by p.partnum asc, o.org
partner idselfbusiness partner100Org1Org1100Org2Org2100Org3Org3101Org1Org1
101Org25Org25102Org2Org2102Org3Org3103Org4Org4104Org1Org1104Org16Org16
I have gotten various Cartesian joins showing every org related to every
other, but have not been unable to properly filter the 'partner
organizations column to exclude 'ego' e.g. the organization that "others"
are related to.
Can anybody offer a solution that selects only the partners that are
linked to each organization?
thanks very much,
glenn B

[Steve Crawford]

Post by b***@comcast.net
Hello,
...

I'm not sure I fully understand the problem and may, therefore,
oversimplify but I'll take a stab.

It sounds like you have organizations and organizations can form
partnerships with one another. My initial approach would be to have a
table of organizations (id plus any necessary data) and a relationships
table to handle the many-to-many issue. The minimum would be two columns:
org1_id, org2_id

Naturally you would want to add whatever constraints are appropriate to
model your allowed relationships.

You could then, if you desire, add a field for relationship type
(competitor, partner, ally, etc.). Depending on the nature of your base
problem, you could even add other tables such as project and include
that data in the relationships table. This would allow two companies to
be competitors on one project but partners on another - a not unusual
situation. You could also include valid dates for relationships or
whatever else you deem necessary.

Given the appropriate recursive query, you could build a full tree of
partnerships for a given organization/project.

Cheers,
Steve

[Kevin Grittner]

Post by b***@comcast.net
I have a list of organizations in table:organization and a one to
many list of organizational partnerships in a second table
(table:partners). The 'partners' table lists each organization id
that belongs to each partnership.
The partnership relationships are recursive in that each partner is
an 'ego' to one or more 'alter' partners, and an alter to other
'ego' partners.

I'm not understanding what's recursive there. Are you saying that
the partner of your partner is effectively also a partner?

Post by b***@comcast.net
self partner id business partner
Org1 100 Org2
Org1 100 Org3
Org2 100 Org1
Org2 100 Org3
Org3 100 Org1
Org3 100 Org2

You could probably generate something like that based on the
following general technique:

create table org (org_id int primary key, org_name text not null);

create table partners (partner_id int, org_id int not null,
primary key (partner_id, org_id),
foreign key (org_id) references org);

insert into org (org_id, org_name) values
(1,'Widgets, Inc.'), (2,'Gadget Corporation'),
(3,'Garply Services'), (25,'Fred Wibble Consulting');

insert into partners (partner_id, org_id) values
(100,1),(100,2),(101,1),(101,25),(102,2),(102,3);

select o1.org_name as self, o2.org_name as "business partner"
from org o1
join partners p1 on (p1.org_id = o1.org_id)
join partners p2 on (p2.partner_id = p1.partner_id
and p2.org_id <> p1.org_id)
join org o2 on (o2.org_id = p2.org_id)
order by self, "business partner";

There are various more complex you could show this, including
something which shows indirect partnerships with a "degrees of
separation" column. I couldn't immediately think of a way to deal
with cycles without setting an arbitrary limit on the recursion depth
and taking the minimum degree of separation. There's probably a
better way to do that.

with recursive rel(dos, id1, id2) as
(
select 1, o1.org_id, o2.org_id
from org o1
join partners p1 on (p1.org_id = o1.org_id)
join partners p2 on (p2.partner_id = p1.partner_id
and p2.org_id <> p1.org_id)
join org o2 on (o2.org_id = p2.org_id)
union all
select r.dos + 1, r.id1, o3.org_id
from rel r
join partners p3 on (p3.org_id = r.id2)
join partners p4 on (p4.partner_id = p3.partner_id
and p4.org_id <> r.id1
and p4.org_id <> r.id2)
join org o3 on (o3.org_id = p4.org_id)
where r.dos <= 20
)
select
o4.org_name as self,
min(r.dos) as dos,
o5.org_name as "business partner"
from rel r
join org o4 on (o4.org_id = r.id1)
join org o5 on (o5.org_id = r.id2)
group by o4.org_name, o5.org_name
order by self, dos, "business partner";

Hopefully this gives you some ideas.

-Kevin