Hierarchy

[thertel00]

There are a few different examples of this on here, but nothing quite like I need to do. I have a table in my DB with an ID, name and parent fields. I need to output this info in my ui but I am having trouble grouping them.

Query results:

ID Name Parent
100 Test NULL
101 Org1 NULL
102 Org2 101
103 Org3 102


I need to dynamically output this info so that the heirarchy is correct.

Example:

100 - Test
101 - Org1
--102 - Org2
----103 - Org3

Any ideas on making this happen with CFQUERY or CFLOOP?

 

[djprice]

Not sure if this is what you want, but try this:

select lpad('.',2*(level-1)) || to_char(child) s
from test_connect_by
start with parent is null
connect by prior child = parent

 

[cfStarlight]

If you have flexibility with the tables, you might be interested in nested sets

http://www.developersdex.com/gurus/articles/112.asp

http://coldfusion.sys-con.com/read/42123.htm

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

HTH