On Wed, Feb 1, 2012 at 3:27 PM, <brooks(dot)glenn(at)comcast(dot)net> wrote:
> 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
> 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'
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 =
where super.name = 'Jones';
Even though it's one table, you can make it look like two tables.
> I would like to create SQL output that looks like this:
> selfpartner idbusiness partnerOrg1100Org2Org1100Org3Org2100Org1Org2100Org3
> (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:
> 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
> order by p.partnum asc, o.org
> the sql returns a duplicate list for self (ego) and business partner
> (alter orgs):
> partner idselfbusiness partner100Org1Org1100Org2Org2100Org3Org3101Org1Org1
> 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 Crawford||Date: 2012-02-01 23:49:06|
|Subject: Re: overcoming a recursive relationship in a sql statement|
|Previous:||From: brooks.glenn||Date: 2012-02-01 23:27:07|
|Subject: overcoming a recursive relationship in a sql statement|