Обсуждение: overcoming a recursive relationship in a sql statement

Поиск
Список
Период
Сортировка

overcoming a recursive relationship in a sql statement

От
brooks.glenn@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:

selfpartner idbusiness partner
Org1100Org2
Org1100Org3
Org2100Org1
Org2100Org3
Org3100Org1
Org3100Org2


(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 partner
100Org1Org1
100Org2Org2
100Org3Org3
101Org1Org1
101Org25Org25
102Org2Org2
102Org3Org3
103Org4Org4
104Org1Org1
104Org16Org16


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






Re: overcoming a recursive relationship in a sql statement

От
Craig James
Дата:


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 partner
Org1100Org2
Org1100Org3
Org2100Org1
Org2100Org3
Org3100Org1
Org3100Org2


(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 partner
100Org1Org1
100Org2Org2
100Org3Org3
101Org1 Org1
101Org25Org25
102Org2Org2
102Org3Org3
103Org4Org4
104Org1 Org1
104Org16Org16


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







Re: overcoming a recursive relationship in a sql statement

От
Steve Crawford
Дата:
On 02/01/2012 03:27 PM, brooks.glenn@comcast.net wrote:
p { margin: 0; }
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