Joe Celko wrote a book called "SQL for Smarties" that has about 20 pages 
devoted to representation of trees in SQL. I believe he also has a website.

His quick take on representing trees in SQL is that hierarchical databases 
do this far better and a RECURSIVE UNION funtion is needed to do this in 
SQL. Which we don't have -- maybe SLQ3?

But, cribbing freely from his book, try this ...

Lets call the table "things"

select D1.Description, "Descrip", P2.parent
    from things as D1, things as P1, things as P2
where D1.Parent =  P1.ID
   and P1.Parent = P2.ID

I don't see how this handles the null values, but I hope it can get you 
started. I'll cheerfully hand this on to persons more expert in SQL than 
myself. HOw you display it, once you have the values fetched is another 

The book is worth getting. ISBN 1-55860-323-9

Regards - Miles Thompson

At 11:27 PM 3/24/01 +1200, you wrote:
>What's the best way to deal with tree structures? Use the following setup in
>a database as an example:
>ID | Parent | Description
>1 |        |   Colours
>2 |    1   |  Red
>3 |        |   Hello
>4 |    1   |  Green
>5 |     4  |  Light
>6 |     4  |  Dark
>This should be represented as:
>1. Colours
>4.   +--Green
>6.       +--Dark
>5.       +--Light
>2.   +--Red
>3. Hello
>(ie, in tree structure and ordered alphabetically by 'descriptiu
>What's the best way to select the data and render it in HTML?
>I thought about going thru each element (of the whole table) at a time, and
>making an array, dynamically adding bits of the tree together until the
>whole tree has been grown ... but it seems a little bit complicated and slow
>... is there anything that could be done [database]server-side which might
>help me?
>Or do other people have code snippets, ideas or anything which might help
>PHP Database Mailing List (
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]

PHP Database Mailing List (
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to