From: Peter Brawley
Date: May 24 2007 1:55pm
Subject: Re: expanding hierarchies
List-Archive: http://lists.mysql.com/mysql/206974
Message-Id: <46559944.2070005@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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