that sounds pretty interesting...
if you wanted to get a node and all its children would you be able to do
that in a single select statement without a loop for each level?
----- Original Message -----
From: "Ben Petersen" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 28, 2002 12:22 AM
Subject: Re: FK to PK on same table MkII
> There was reference to a Joe Celko article where he discussed "a
> modified reverse tree transversal algorithm" or something similar,
> was also called something like "leaf and node".
>
> Parent and child relationships are tracked in one table. Each
> member has two integer references which bound subordinate
> member's integers. Relationships are added or changed by
> increasing/ decreasing these integers for the effected member and
> subordinates. It took an evening of messing around with the
> concept to finally "get it", but it's pretty cool.
>
> Searching the archives for Celko will probably dredge it up. If not, I
> have some light (very) code I think can find and pass on that would
> provide an example (if i remember correctly).
>
> Ben Petersen
>
>
>
> On 27 Oct 2002, at 23:43, Bernie Corrigan wrote:
>
> > Hi Tom -
> >
> > This is the multi-level parts breakout problem. We discussed
> > this
> > on the list way back when and I can't locate the solution; however,
> > there is one and you might already have it in your archive.
> >
> > Bernie
> >
> > ----------------------------------------
> > At 09:09 AM 10/27/2002 +1100, you wrote:
> > >G'day,
> > >
> > >I said:
> > >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > >I am wanting to create a parent child relationship
> > >between trading entities in the same table so I can
> > >allow users to report on just one trading entity or
> > >all under multiple levels of holding companies.
> > >
> > >I have created the table TradingEntity and the PK
> > >col is EntityID. I have just programmatically
> > >added the col ParentEntityID to the table with a
> > >FK on ParentEntityID to reference EntityID.
> > >
> > >When I try to make another structural change in the
> > >object manager (delete SYS_ROWVER) I get the error
> > >message [The referenced table TradingEntity does not
> > >have a compatible Primary Key.]
> > >
> > >Which is the bug, allowing me to programmatically
> > >create the FK or not allowing me to edit the table
> > >after it is done?
> > >
> > >I know I can do it with a RULE, just interested to
> > >know the "proper" way to do it in R:BASE.
> > >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > >But I was wrong.
> > >
> > >With this:
> > >
> > >CREATE TABLE `TradingEntity` +
> > >(`EntityID` INTEGER NOT NULL +
> > >('Value for EntityID cannot be null.') , +
> > > `Org_ID` INTEGER NOT NULL +
> > >('Value for Org_ID cannot be null.') , +
> > > `HeaderReqs` TEXT (70) , +
> > > `EntityName` TEXT (70) , +
> > > `AddressID` INTEGER , +
> > > `CNRowIDTel` INTEGER , +
> > > `CNRowIDFax` INTEGER , +
> > > `CNRowIDEmail` INTEGER , +
> > > `CNRowIDURL` INTEGER , +
> > > `EntityNote` TEXT (200) , +
> > > `CrntAvailable` TEXT (1) DEFAULT 'Y' NOT NULL , +
> > > `ParentEntityID` INTEGER , +
> > > `SYS_ROWVER`= +
> > > (IFNULL((SYS_ROWVER+1),0,(SYS_ROWVER+1))+
> > >) INTEGER )
> > >ALTER TABLE `TradingEntity` ADD PRIMARY KEY +
> > >(`EntityID` ) +
> > >('Values for rows in TradingEntity must be unique.',+
> > > 'Cannot delete - values exist in another table.',+
> > > 'Cannot update - values exist in another table.')
> > >AUTONUM `EntityID` IN `TradingEntity` USING +
> > >4. 1. NONUM
> > >RULES 'Value must exist in TradingEntity ' +
> > > FOR `TradingEntity` SUCCEEDS +
> > > WHERE EXISTS (SELECT EntityID FROM TradingEntity
> > > WHERE T+
> > >radingEntity.EntityID = TradingEntity.ParentEntity+
> > >ID )
> > >
> > >In the browser I cannot get the existing values
> > >for EntityID accepted into ParentEntityID without
> > >a rule violation message on exit from the last
> > >row in the browser.
> > >
> > >Any clues?
> > >
> > >Warmest regards,
> > >
> > >
> > >Tom Grimshaw
> > >coy: Just For You Software
> > >tel: 612 9552 3311
> > >fax: 612 9566 2164
> > >mobile: 0414 675 903
> > >
> > >post: PO Box 470 Glebe NSW 2037 Australia
> > >street: 3/66 Wentworth Park Rd Glebe NSW 2037
> > >
> > >email: [EMAIL PROTECTED]
> > >web: www.just4usoftware.com.au
> > >
> > >"... the control of impulse -- is the first principle of
> > >civilization."-- Will Durant, Pulitzer Prize winning philosopher,
> > >writer and historian
> > >
> > >the most needed product in the world can be found at
> > >www.thewaytohappiness.org
> > >
> > >This email and any files transmitted with it are confidential to the
> > >intended recipient and may be privileged. If you have received this
> > >email inadvertently or you are not the intended recipient, you may
> > >not disseminate, distribute, copy or in any way rely on it. Further,
> > >you should notify the sender immediately and delete the email from
> > >your computer. Whilst we have taken precautions to alert us to the
> > >presence of computer viruses, we cannot guarantee that this email and
> > >any files transmitted with it are free from such viruses.
> > >
> > >================================================
> > >TO SEE MESSAGE POSTING GUIDELINES:
> > >Send a plain text email to [EMAIL PROTECTED]
> > >In the message body, put just two words: INTRO rbase-l
> > >================================================
> > >TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In
> > >the message body, put just two words: UNSUBSCRIBE rbase-l
> > >================================================ TO SEARCH ARCHIVES:
> > >http://www.mail-archive.com/rbase-l%40sonetmail.com/
> > >
> > >================================================
> > >TO SEE MESSAGE POSTING GUIDELINES:
> > >Send a plain text email to [EMAIL PROTECTED]
> > >In the message body, put just two words: INTRO rbase-l
> > >================================================
> > >TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In
> > >the message body, put just two words: UNSUBSCRIBE rbase-l
> > >================================================ TO SEARCH ARCHIVES:
> > >http://www.mail-archive.com/rbase-l%40sonetmail.com/
> > >
> >
> > ================================================
> > TO SEE MESSAGE POSTING GUIDELINES:
> > Send a plain text email to [EMAIL PROTECTED]
> > In the message body, put just two words: INTRO rbase-l
> > ================================================
> > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In
> > the message body, put just two words: UNSUBSCRIBE rbase-l
> > ================================================ TO SEARCH ARCHIVES:
> > http://www.mail-archive.com/rbase-l%40sonetmail.com/
> >
>
>
> ================================================
> TO SEE MESSAGE POSTING GUIDELINES:
> Send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: INTRO rbase-l
> ================================================
> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: UNSUBSCRIBE rbase-l
> ================================================
> TO SEARCH ARCHIVES:
> http://www.mail-archive.com/rbase-l%40sonetmail.com/
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
