overcoming a recursive relationship in a sql statement

[brooks.glenn@xxxxxxxxxxx]

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

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

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