Re: overcoming a recursive relationship in a sql statement

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
>
>
>
>
>
>
>