pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedovercoming a recursive relationship in a sql statement
4+ messages / 4 participants
[nested] [flat]
* overcoming a recursive relationship in a sql statement
@ 2012-02-01 23:27 brooks.glenn@comcast.net
2012-02-01 23:37 ` Re: overcoming a recursive relationship in a sql statement Craig James <cjames@emolecules.com>
2012-02-01 23:49 ` Re: overcoming a recursive relationship in a sql statement Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 2 replies; 4+ messages in thread
From: brooks.glenn@comcast.net @ 2012-02-01 23:27 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: overcoming a recursive relationship in a sql statement
2012-02-01 23:27 overcoming a recursive relationship in a sql statement brooks.glenn@comcast.net
@ 2012-02-01 23:37 ` Craig James <cjames@emolecules.com>
1 sibling, 0 replies; 4+ messages in thread
From: Craig James @ 2012-02-01 23:37 UTC (permalink / raw)
To: brooks.glenn@comcast.net; +Cc: pgsql-admin
On Wed, Feb 1, 2012 at 3:27 PM, <brooks.glenn@comcast.net> wrote:
> 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'm not positive from your description, but it seems like the concept you
are missing is that a single table can have several aliases in one SQL
query, which makes it appear to be several different tables. Take a simple
example: a "personnel" table with employees and supervisors. Find
everyone with supervisor "Jones":
select name from personnel emp join personnel super on (emp.supervisor =
super.name)
where super.name = 'Jones';
Even though it's one table, you can make it look like two tables.
Craig
>
> I would like to create SQL output that looks like this:
>
> selfpartner idbusiness partnerOrg1100Org2Org1100Org3Org2100Org1Org2100Org3
> Org3100Org1Org3100Org2
>
>
> (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 idselfbusiness partner100Org1Org1100Org2Org2100Org3Org3101Org1Org1
> 101Org25Org25102Org2Org2102Org3Org3103Org4Org4104Org1Org1104Org16Org16
>
>
> 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
>
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: overcoming a recursive relationship in a sql statement
2012-02-01 23:27 overcoming a recursive relationship in a sql statement brooks.glenn@comcast.net
@ 2012-02-01 23:49 ` Steve Crawford <scrawford@pinpointresearch.com>
1 sibling, 0 replies; 4+ messages in thread
From: Steve Crawford @ 2012-02-01 23:49 UTC (permalink / raw)
To: brooks.glenn@comcast.net; +Cc: pgsql-admin
On 02/01/2012 03:27 PM, brooks.glenn@comcast.net wrote:
> Hello,
>
> ...
>
I'm not sure I fully understand the problem and may, therefore,
oversimplify but I'll take a stab.
It sounds like you have organizations and organizations can form
partnerships with one another. My initial approach would be to have a
table of organizations (id plus any necessary data) and a relationships
table to handle the many-to-many issue. The minimum would be two columns:
org1_id, org2_id
Naturally you would want to add whatever constraints are appropriate to
model your allowed relationships.
You could then, if you desire, add a field for relationship type
(competitor, partner, ally, etc.). Depending on the nature of your base
problem, you could even add other tables such as project and include
that data in the relationships table. This would allow two companies to
be competitors on one project but partners on another - a not unusual
situation. You could also include valid dates for relationships or
whatever else you deem necessary.
Given the appropriate recursive query, you could build a full tree of
partnerships for a given organization/project.
Cheers,
Steve
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: overcoming a recursive relationship in a sql statement
@ 2012-02-05 05:11 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 4+ messages in thread
From: Kevin Grittner @ 2012-02-05 05:11 UTC (permalink / raw)
To: brooks.glenn@comcast.net; pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-02-05 05:11 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-01 23:27 overcoming a recursive relationship in a sql statement brooks.glenn@comcast.net
2012-02-01 23:37 ` Craig James <cjames@emolecules.com>
2012-02-01 23:49 ` Steve Crawford <scrawford@pinpointresearch.com>
2012-02-05 05:11 Re: overcoming a recursive relationship in a sql statement Kevin Grittner <Kevin.Grittner@wicourts.gov>
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