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 >>> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
