Hey Hans
I hope the select statement that I am sending to you
fixes the problem. You can use a combination of left
joins and ordering by a dummy key.
Running your table in Oracle [(+) is left join]
select p2.pagetitle,p2.pageId, p2.parentId,
p.parentId||p1.parentId||p2.parentId||p2.pageId as
mykey
from pages p, pages p1, pages p2
where p2.parentId = p1.pageId(+)
and p1.parentId = p.pageId(+)
order by myKey
I will get what you want (difficult to paste from
sql+)
(any questions let me know)
PAGETITLE PAGEID PARENTID MYKEY
---------------------------- --------- --------- -----
Home 1 0 01
Main 1 2 1 012
Sub 1a 3 2
0123
Sub 2a 4 2
0124
Main 2 5 1 015
Sub 1b 6 5
0156
Sub 2b 7 5
0157
7 rows selected.
--- Hans Omli <[EMAIL PROTECTED]> wrote:
> 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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
