[AccessD] Tree shaped reports: Joe Celco BOM

Hi Bruce

Psychic? That was new!

Anyway, I would of course like see those algorithms.
Celko mumbles in his articles about pseudo code and differences
between the DB engines as an excuse for not providing real code and
leaving it as "an exercise for the reader" ...

/gustav


> You must be psychic!  I was just retrieving the db that I used the
> previous stuff in to apply again today in a new explosion situation.

> Regarding maintainance of the nested set stuff,  its actually quite easy
> once you have the insertion and deletion rules stated as alorithms
> (psuedo code) as the pointer handling is very standard.

> I have these algotithms somewhere!!!! They were in the course notes from
> my university course on data structures.  Unfortunately, where somewhere
> is is not handy.  I think they are at my mums place(?) I will look for
> them when next I am there, which could be tomorrow or could be next
> month.

> Regards
> Bruce

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Tuesday, 15 July 2003 8:43 PM
> To: AccessD at databaseadvisors.com
> Subject: Re: [AccessD] Tree shaped reports: Joe Celco BOM


> Hi Bruce And Arthur

> Just located this link with the Access 2000 example database:

>   http://www.mvps.org/access/queries/qry0023.htm

> for the nested tree lookup without recursion.

> In addition to the link below:

>   http://www.dbmsmag.com/9603d06.html

> these deals with the topic too:

>   http://www.dbmsmag.com/9604d06.html
>   http://www.dbmsmag.com/9605d06.html

> While the idea of a nested tree and pure SQL is nice, maintenance seems
> a bit difficult ...

> /gustav


>> Gustav!

>> You're worth bottling mate!  It worked like a charm (after a slight 
>> change to cope with multiple "root" nodes).  Please find firstborn 
>> male child attached as a token of my thanks.

>> And thanks to everyone else for input on this topic! - except maybe 
>> Charlotte who has now got me reading MSDN data shaping doco's :-)

>> And Arthur, at your suggestion I have just read several of Joe Celko's

>> articles available online, in particular 
>> http://www.dbmsmag.com/9603d06.html
>> discussing the nested-set tree model.  Strange, I learnt all about
>> this in
>> Grad School in 1975 - don't know how I could have forgotten
>> it....hahaha.

>> Thanks to all again!
>> Bruce