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
exercise.
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
>me??
>
>Thanks,
>
>Siggy
>
>
>
>
>--
>PHP Database Mailing List (http://www.php.net/)
>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 (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
