Re: overcoming a recursive relationship in a sql statement

wrote:

> 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?

> 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

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