Parent child model 2

[newbby]

All,
I have this below resultset derived from one table and in one query. It can have any levels of parent-child, parent-subparent-child relation .I cannot make multiple calls to the database.
ItemID Item ParentItemID
1 Colors 0
2 Animals 0
3 Dog 2
4 Spaniel 3
5 Blue 1
6 People 0
7 Steve 6
8 Red 1
9 Cat 2
10 Bob 6
11 Doberman 3
12 Green 8
Can anyone advise me how I can get the child to be displayed under the parent as this with formatting:
ItemID Item ParentItemID
1 Colors 0
5 Blue 1
8 Red 1
12 Green 8
2 Animals 0
3 Dog 2
4 Spaniel 3
11 Doberman 3
9 Cat 2
6 People 0
7 Steve 6
10 Bob 6

Thanks

 

[r937]

indenting will have to be performed by cf

retrieval of data can be achieved in one query provided that you know the maximum number of levels that the tree will span

from the sample data, you have 3 levels, so you'd use a query with the table joined to itself twice

let me know if you want the query

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[newbby]

Thanks Rudy, The join query provides me with the resultset but I am lost as far as formatting and displaying the same as parent/child on the .cfm page.

 

[r937]

the format of the following examples is indented style, although other options are possible

2-level and 3-level solutions are shown

note this can easily be extended to 4, 5, or more levels

i've heard of tests up to 15 levels deep with no loss of performance, however, in my opinion if you have a hierarchy that is more than say 5 levels deep, or more than 3 for a navigation hierarchy on the web or similar user interface(i.e. categories, sub-categories, sub-sub-categories, sub-sub-sub-categories, etc.) then you do not have a technical or programming problem, you have an information architecture or taxonomy problem

2-level solution --

<cfquery name="gettree">
select parent.ItemID  as parentID
     , parent.Item    as parentItem  
     , child.ItemID   as childID 
     , child.Item     as childItem
  from yourtable as parent
left outer
  join yourtable as child
    on parent.ItemID
     = child.ParentItemID 
 where parent.ParentItemID is null
order 
    by parent.ItemID 
     , child.ItemID 
</cfquery>     
<cfoutput query="gettree" group="ParentID">
<br>(#parentID#) #parentItem#
<cfoutput>   
<br> &nbsp; (#childID#) #childItem#
</cfoutput>
</cfoutput>

3-level solution --

<cfquery name="gettree">
select parent.ItemID  as parentID
     , parent.Item    as parentItem  
     , child.ItemID   as childID 
     , child.Item     as childItem
     , grchild.ItemID   as grchildID 
     , grchild.Item     as grchildItem
  from yourtable as parent
left outer
  join yourtable as child
    on parent.ItemID
     = child.ParentItemID 
left outer
  join yourtable as grchild
    on child.ItemID
     = grchild.ParentItemID
 where parent.ParentItemID is null
order 
    by parent.ItemID 
     , child.ItemID 
     , grchild.ItemID 
</cfquery>     
<cfoutput query="gettree" group="parentID">
<br>(#parentID#) #parentItem#
<cfoutput group="childID">  
<br> &nbsp; (#childID#) #childItem#
<cfoutput>   
<br> &nbsp; &nbsp; (#grchildID#) #grchildItem#
</cfoutput>
</cfoutput>
</cfoutput>

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[imstillatwork]

thats a star from me...

good example