OT: Nested Trees in a database

[Hans Omli]

I need some help with nested trees in a database, as discussed in chapter 9
of "Fusebox: Methodology and Techniques".  The following code, based off the
code in the book, doesn't return a nested tree.  Instead, it returns the
root page's pageid and pagetitle several times.

<cfquery name="nested" datasource="#request.maindsn#">
        SELECT p.pageid, p.pagetitle
                FROM pages p, pages p1, pages p2
                WHERE p1.parentid = 0
                        AND p1.pageid = p.pageid
                        AND p2.parentid = p1.pageid
                ORDER BY p.pagetitle
</cfquery>

My tables are based on:

<cfquery datasource="#request.maindsn#">
        CREATE TABLE pages
        (
                pageid  INTEGER NOT NULL,
                parentid        INTEGER NOT NULL,
                pagetitle       VARCHAR(50)     NOT NULL
        );
</cfquery>

I have the following records in the table:

PAGEID  PARENTID        PAGETITLE
1               0               Home
2               1               Main 1
3               2               Sub 1a
4               2               Sub 2a
5               1               Main 2
6               5               Sub 1b
7               5               Sub 2b

Thus, the output I am expecting is:

Home
        Main 1
                Sub 1a
                Sub 2a
        Main 2
                Sub 1b
                Sub 2b

What am I doing wrong?


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/fusebox@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists