Message-Id: <4F2DBB270200002500044DD8@gw.wicourts.gov>
Date: Sat, 04 Feb 2012 23:11:35 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <brooks.glenn@comcast.net>,<pgsql-admin@postgresql.org>
Subject: Re: overcoming a recursive relationship in a sql
 statement
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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