Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 6405A420B3E for ; Wed, 1 Feb 2012 19:27:21 -0400 (AST) Received: from qmta09.emeryville.ca.mail.comcast.net ([76.96.30.96]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RsjaD-0005ai-0b for pgsql-admin@postgresql.org; Wed, 01 Feb 2012 23:27:21 +0000 Received: from omta16.emeryville.ca.mail.comcast.net ([76.96.30.72]) by qmta09.emeryville.ca.mail.comcast.net with comcast id Uem61i0011ZMdJ4A9nT77H; Wed, 01 Feb 2012 23:27:07 +0000 Received: from sz0007.ev.mail.comcast.net ([76.96.26.53]) by omta16.emeryville.ca.mail.comcast.net with comcast id UnT71i00b18ke3w8cnT73V; Wed, 01 Feb 2012 23:27:07 +0000 Date: Wed, 1 Feb 2012 23:27:07 +0000 (UTC) From: brooks.glenn@comcast.net To: pgsql-admin@postgresql.org Message-ID: <835571162.336754.1328138827494.JavaMail.root@sz0007a.emeryville.ca.mail.comcast.net> In-Reply-To: <1010584744.322646.1328121791854.JavaMail.root@sz0007a.emeryville.ca.mail.comcast.net> Subject: overcoming a recursive relationship in a sql statement MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_336753_362437130.1328138827467" X-Originating-IP: [76.22.112.255] X-Mailer: Zimbra 6.0.13_GA_2944 (ZimbraWebClient - SAF3 (Win)/6.0.13_GA_2944) X-Pg-Spam-Score: -0.9 (/) X-Archive-Number: 201202/2 X-Sequence-Number: 39062 ------=_Part_336753_362437130.1328138827467 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit 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 ------=_Part_336753_362437130.1328138827467 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable <= div style=3D'font-family: Arial; font-size: 12pt; color: #000000'>Hello,

So I am not very advanced in SQL, and after a week of wild SQL exper= imenting 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 partnership= s in a second table (table:partners).  The 'partners' table lists each= organization id that belongs to each partnership. 
<= font face=3D"Arial" size=3D"3">
The partnership relationships are recursive in that each partner = is an 'ego' to one or more 'alter' partners, and an alter to other 'ego' pa= rtners.

<= div>So I havent been able to figure out a w= ay to select business partners 'alter' organizations that are distinctly re= lated to each 'ego' organization.    

 I would like to create  SQL output that looks like this:<= /font>

=
selfpartner idbusiness partner
Org1100Org2
Org1100Org3
Org2100= Org1<= /td>
Org2100Org3
Org3100Org1
Org3100Org2


(The futher  complexities are that every combination of partners= hips 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 pa= rtnership 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 &nbs= p;               xyz
org2=09=09abc
org3=09=09<= /span>blah blah
orgx=09=09=09etc

&nbs= p;     Table: partners
---------------------------------------=09=09=09=09=09=09
partnerid        = member_org
100=09=09=09=09org1
100=09=09=09=09org2
101=09=09=09=09org1
101=09= =09=09=09org25
102<= span class=3D"Apple-tab-span" style=3D"white-space:pre">=09=09=09=09= org2
102=09=09=09=09org3

tab= le: affiliation     unique constraints =3D (partner, competitor, = ally)
--------------------= -----------------
affiliat= ion_id=09=09affiliation
100=09=09=09=09partner
101=09=09=09=09competitor
=
102=09=09=09=09ally
103=09=09=09=09competitor
= 1xx  =09=09=09=09= etc

=
Each organization is connected to other org(s) as a business part= ner or  competitor. (which I ignore for the moment). 

I hav= e tried a variety of self joins, and many to many joins, to no avail.&= nbsp;

I have no idea how to select "business partn= ers" 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 partn= ers p on o.org =3D p.member_id
join 
order by p.pa= rtnum asc, o.org

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

Org1101= Org4<= /tr>

I have gotten various Cartesian joins showing ever= y org related to every other, but have not been unable to properly filter t= he 'partner organizations column to exclude 'ego' e.g. the organization tha= t "others" are related to. 
Can anybody offer a solution that  selects only the partners t= hat are linked to each organization?  

thanks very much,

glenn B






p= artner idself<= /td>business partne= r
100Org1
100Org2Org2
Org3Org3
Org1101Org25<= /td>Org25
102Org2Org2
102Org3Org3
103Org4
104= Org1Org1
104Org16Org16
=
------=_Part_336753_362437130.1328138827467--