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
>>>
>
