From: brooks.glenn@comcast.net
To: pgsql-admin@postgresql.org
Subject: overcoming a recursive relationship in a sql statement
Date: Wed, 1 Feb 2012 23:27:07 +0000 (UTC)
Message-ID: <835571162.336754.1328138827494.JavaMail.root@sz0007a.emeryville.ca.mail.comcast.net> (raw)
In-Reply-To: <1010584744.322646.1328121791854.JavaMail.root@sz0007a.emeryville.ca.mail.comcast.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: brooks.glenn@comcast.net
Subject: Re: overcoming a recursive relationship in a sql statement
In-Reply-To: <835571162.336754.1328138827494.JavaMail.root@sz0007a.emeryville.ca.mail.comcast.net>
References: <1010584744.322646.1328121791854.JavaMail.root@sz0007a.emeryville.ca.mail.comcast.net> <835571162.336754.1328138827494.JavaMail.root@sz0007a.emeryville.ca.mail.comcast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox