List:General Discussion« Previous MessageNext Message »
From:Naz Gassiep Date:May 24 2007 4:16pm
Subject:Re: expanding hierarchies
View as plain text  
The definitive answer to anything that requires trees in SQL is nested
sets. I have written a tutorial on the subject, as this is about the
most asked question in DB relational data modeling.

http://www.mrnaz.com/static/articles/trees_in_sql_tutorial/

Enjoy :)

- Naz.

Peter Brawley wrote:
> tbt
>
> >the number of levels in this table is unknown and the query should work
>>  for any number of levels
>>  please provide a sample 'select' query in mysql
>
> That's a graph, which is recursive, so you need an sproc. See edge
> list sprocs at
> http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html.
>
> PB
>
> -----
>
> tbt wrote:
>>> Hi
>>>
>>> i have a table like this
>>>
>>> id      name        parent_id
>>> 1       europe
>>> 2       spain       1
>>> 3       england     1
>>> 4       france      1
>>> 5       london      3
>>> 6       mayfair     5
>>> 7       madrid      2
>>>
>>> in this table each destination is mapped to a parent destination
>>> eg: mayfair is mapped to london, london is mapped to england etc.
>>>
>>> i like to write a query to find child destinations when an id is
>>> given for
>>> a parent destination
>>> eg: if 3 is the id, then the result of query should be 3,5,6
>>> if 2 is the id, then the result should be 2,7
>>>
>>> the number of levels in this table is unknown and the query should work
>>> for any number of levels
>>> please provide a sample 'select' query in mysql
>>>     
>
Thread
expanding hierarchiestbt24 May
  • Re: expanding hierarchiesPeter Brawley24 May
    • Re: expanding hierarchiesNaz Gassiep24 May
      • Re: expanding hierarchiesPeter Brawley24 May
        • Re: expanding hierarchiesNaz Gassiep24 May
Re: expanding hierarchiesPeter Brawley24 May