On Wed, Feb 1, 2012 at 3:27 PM, <brooks.gl...@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 > > > > > > >
