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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin