List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 2 2006 9:14pm
Subject:Re: logic/db question
View as plain text  
Bruce,

>my question, how can i come up with a sql query that will list all the
>children (and children's children...) of a top level item?

This is an edge list tree model, ID being the child node, parentID being 
the parent node, the row denoting the edge between ID and parentID. 
Unless you know in advance how many levels there are, you need an sproc 
to retrieve subtrees.There is an example with discussion in Listing 7 at 
http://localhost/artful/mysqlbook/sampler/mysqled1ch20.html.

PB

-----

bruce wrote:
> hi...
>
> i have a tbl
>  fooTBL
>    name
>    parentID
>    ID
>
> so a name can have might have a parentID, as well as an ID. 'name's are
> associated with other 'name's via the parentID. in other words, if a name's
> parentID == a name's ID, name1 is the parent of name2.
>
> ie
>
>  	name		parentID		ID
> 	stanford	''			1
> 	fall-06	 1			2
> 	spring-06	 1			3
> 	summer-06	 1			4
> 	acct		 2			5
> 	biol		 2			6
> 	math		 2			7
> 	acct		 3			8
> 	biol		 3			9
> 	math		 3			10
> 	acct		 4			11
> 	biol		 4			12
> 	math		 4			13
> 	stanford	''			14
> 	fall-06	 14			15
> 	spring-06	 14			16
> 	summer-06	 14			17
> 	acct		 15			18
> 	biol		 15			19
> 	math		 15			20
>    	etc....
>
> my question, how can i come up with a sql query that will list all the
> children (and children's children...) of a top level item?
>
> searching google gives some insight into how to handle recursion/tree
> issues. i haven't as of yet come across anything that gets me to where i
> need to be. can this be handled with only a single table?
>
> i also need the select/delete/insert queries to be reasonably fast..
>
> i had done this awhile ago.. but can't recall how i did it..
>
> thanks
>
> -bruce
>
>
>   


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.5/404 - Release Date: 7/31/2006

Thread
logic/db questionbruce2 Aug
  • Re: logic/db questionPeter Brawley2 Aug
    • RE: logic/db questionbruce2 Aug
      • Re: logic/db questionPeter Brawley2 Aug
      • FULL TEXT SEARCH ALTERNATIVES...avrombay3 Aug
        • Re: FULL TEXT SEARCH ALTERNATIVES...Martin Jespersen3 Aug
        • Re: FULL TEXT SEARCH ALTERNATIVES...mos3 Aug