Skip site navigation (1) Skip section navigation (2)

overcoming a recursive relationship in a sql statement

From: brooks(dot)glenn(at)comcast(dot)net
To: pgsql-admin(at)postgresql(dot)org
Subject: overcoming a recursive relationship in a sql statement
Date: 2012-02-01 23:27:07
Message-ID: 835571162.336754.1328138827494.JavaMail.root@sz0007a.emeryville.ca.mail.comcast.net (view raw or flat)
Thread:
Lists: pgsql-admin
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 












Responses

pgsql-admin by date

Next:From: Craig JamesDate: 2012-02-01 23:37:27
Subject: Re: overcoming a recursive relationship in a sql statement
Previous:From: Al-Salami, AdelDate: 2012-02-01 12:59:31
Subject: postgresql failed to establish a connection to <pg server name>

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group