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