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

Re: overcoming a recursive relationship in a sql statement

From: Craig James <cjames(at)emolecules(dot)com>
To: brooks(dot)glenn(at)comcast(dot)net
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: overcoming a recursive relationship in a sql statement
Date: 2012-02-01 23:37:27
Message-ID: CAFwQ8reupaN3zwTbEfEiAXdabm2Ki5KzN9owv7fov14aHz5vnQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
On Wed, Feb 1, 2012 at 3:27 PM, <brooks(dot)glenn(at)comcast(dot)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
>
>
>
>
>
>
>

In response to

pgsql-admin by date

Next:From: Steve CrawfordDate: 2012-02-01 23:49:06
Subject: Re: overcoming a recursive relationship in a sql statement
Previous:From: brooks.glennDate: 2012-02-01 23:27:07
Subject: overcoming a recursive relationship in a sql statement

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