>I'm having a lot of difficulty trying to figure this out. I have a table
>with a list of projects that I would like to arrange and view as a tree.
>This is my table:
>
>+--------+------------------+------+-----+---------+----------------+
>| Field | Type | Null | Key | Default | Extra |
>+--------+------------------+------+-----+---------+----------------+
>| id | int(10) unsigned | | PRI | NULL | auto_increment |
>| name | varchar(64) | | MUL | | |
>| handle | varchar(18) | | | | |
>| parent | int(10) unsigned | | | 0 | |
>| status | varchar(64) | | | | |
>| data | text | YES | | NULL | |
>+--------+------------------+------+-----+---------+----------------+
>
>This is what I want to get in the end:
>
>
> + root project
> |
> + project 1
> | |
> | + sub project 1.1
> | + sub project 1.2
> | |
> | + sub project 1.2.1
> |
> + project 2
> |
> + sub project of 2.1
> + sub project of 2.2
>
>
>What SQL statement(s) would retrieve my records, ordered by project ID, but
>grouped recursively into a tree? Or, is this something not possible through
>SQL itself?
>
>Thanks,
>
>
>
>Davis
Sir, SQL can handle trees. Regular readers of this list will be very
surprised to see me recommend the book 'SQL For Smarties' by Joe
Celko. There are two chapters on tree structures. You seem to be
using the adjacency list model, so read that chapter first. Parts of
his code assume a procedural language (e.g. PL/SQL or Transact-SQL),
so you will need a client app to fully implement the solutions using
MySQL.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
