RE: OT: SQL--Recursive Query Question

2003-06-03 Thread Andre Turrettini
Subject: Re: OT: SQL--Recursive Query Question > > > Ian Skinner wrote: > > Any suggestions if you where doing something like this, but did not > > know the number of levels before hand? > > The presented model (adjacency list model) can be easily extended as &g

Re: OT: SQL--Recursive Query Question

2003-05-31 Thread Jochem van Dieten
Ian Skinner wrote: > Any suggestions if you where doing something like this, but did not know the > number of levels before hand? The presented model (adjacency list model) can be easily extended as deep as you want by adding more left joins, but performance will suffer if you add too many. And

Re: OT: SQL--Recursive Query Question

2003-05-31 Thread Jochem van Dieten
Terry Hogan wrote: > Getting closer... > > Was perfect with the original data, but I added a new subcategory (Horses) > and a new main category (Colors). I presumed they were all fully filled (3 levels deep). But if they are not, you need to test for empty values in the output then: #

RE: OT: SQL--Recursive Query Question

2003-05-31 Thread Terry Hogan
olors/ Colors// Colors/// -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Friday, May 30, 2003 2:55 PM To: CF-Talk Subject: Re: OT: SQL--Recursive Query Question SELECT a.categoryname AS cat1, b.categoryname AS cat2, c.categoryname AS cat3

RE: OT: SQL--Recursive Query Question

2003-05-31 Thread Ian Skinner
-Talk Subject: Re: OT: SQL--Recursive Query Question Terry Hogan wrote: > Here's the output: > > animals/ > animals/Cats/ > animals/Cats/Tabby/ > animals/Dogs/ > animals/Dogs/Beagle/ > animals/Dogs/German Shepard/ > Beagle/ > Beagle// > Beagle/// >

Re: OT: SQL--Recursive Query Question

2003-05-31 Thread Jochem van Dieten
Terry Hogan wrote: > Here's the output: > > animals/ > animals/Cats/ > animals/Cats/Tabby/ > animals/Dogs/ > animals/Dogs/Beagle/ > animals/Dogs/German Shepard/ > Beagle/ > Beagle// > Beagle/// > Cats/ > Cats/Tabby/ > Cats/Tabby// > Dogs/ > Dogs/Beagle/ > Dogs/Beagle// > Dogs/German Shepard/ > Dog

RE: OT: SQL--Recursive Query Question

2003-05-31 Thread Terry Hogan
ge- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Friday, May 30, 2003 1:54 PM To: CF-Talk Subject: Re: OT: SQL--Recursive Query Question Terry Hogan wrote: > > > SELECT > a.categoryID, > a.CategoryID catID3, a.categoryname cat3, a.level level3, >

Re: OT: SQL--Recursive Query Question

2003-05-31 Thread Jochem van Dieten
Terry Hogan wrote: > > > SELECT > a.categoryID, > a.CategoryID catID3, a.categoryname cat3, a.level level3, > b.categoryID catID2, b.categoryname cat2, b.level level2, > c.CategoryID catID1, c.categoryname cat1, c.level level1 > FROM > tblCategory a LEFT JOIN >

RE: OT: SQL--Recursive Query Question

2003-05-31 Thread Terry Hogan
/German Shepard But can't figure out a way to get that and this works fine. Thanks again, Terry -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Friday, May 30, 2003 3:59 AM To: CF-Talk Subject: Re: OT: SQL--Recursive Query Question Terry Hogan w

Re: OT: SQL--Recursive Query Question

2003-05-30 Thread Jochem van Dieten
Terry Hogan wrote: > > CategoryID ParentIDLevel CategoryName > -- - > 1 0 1 Animals > 2 1 2 Dogs > 3 2 3 GermanShepard > 4 1

OT: SQL--Recursive Query Question

2003-05-30 Thread Terry Hogan
I have this table Structure tblCategory --- CategoryID ParentID Level CategoryName with this data CategoryID ParentIDLevel CategoryName -- - 1 0 1 Animals 2 1 2 Dog