Обсуждение: logic/db question
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. name parentID ID foo - 1 cat 1 2 dog 2 3 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? i had done this awhile ago.. but can't recall how i did it.. thanks
Typically, a temp table is used for this. Beware of circular references. Fred -> Wilma -> Betty -> Barney -> Fred > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of bruce > Sent: Wednesday, August 02, 2006 11:55 AM > To: 'PgSQL-General' > Subject: [GENERAL] logic/db question > > 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. > > name parentID ID > foo - 1 > cat 1 2 > dog 2 3 > > 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? > > i had done this awhile ago.. but can't recall how i did it.. > > thanks > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
On 8/2/06, bruce <bedouglas@earthlink.net> 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. > > name parentID ID > foo - 1 > cat 1 2 > dog 2 3 > > 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? > > i had done this awhile ago.. but can't recall how i did it.. > you can try tablefunc contrib module, or recursive pl/pgsql http://people.planetpostgresql.org/merlin/index.php?/archives/2-Dealing-With-Recursive-Sets-With-PLPGSQL.html merlin
If table reorganization is an option for you, you could consider using integer ranges for describing parant/children relationships. I have seen them in one of Joe Celko books: http://www.amazon.com/gp/product/1558609202/sr=8-2/qid=1154595988/ref=pd_bbs_2/104-2243146-1376759?ie=UTF8 In that way, you can find all the children with just one query. Regards Marco On 8/2/06, bruce <bedouglas@earthlink.net> 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. > > name parentID ID > foo - 1 > cat 1 2 > dog 2 3 > > 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? > > i had done this awhile ago.. but can't recall how i did it.. > > thanks > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Marco Bizzarri http://notenotturne.blogspot.com/