The below piece is from the SQL books online and provides a sample of
handling expanding heirarchies. This may be what you are looking for
(though it will probably only work in SQL, not access)...
Expanding Hierarchies
Databases often store hierarchical information. For example, the following
data is a hierarchical representation of regions of the world. This
representation does not clearly show the structure implied by the data.
Parent Child
---------------------------------- ----------------------------------
World Europe
World North America
Europe France
France Paris
North America United States
North America Canada
United States New York
United States Washington
New York New York City
Washington Redmond
This example is easier to interpret:
World
North America
Canada
United States
Washington
Redmond
New York
New York City
Europe
France
Paris
The following Transact-SQL procedure expands an encoded hierarchy to any
arbitrary depth. Although Transact-SQL supports recursion, it is more
efficient to use a temporary table as a stack to keep track of all of the
items for which processing has begun but is not complete. When processing is
complete for a particular item, it is removed from the stack. New items are
added to the stack as they are identified.
CREATE PROCEDURE expand (@current char(20)) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item char(20), level int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE level = @level
SELECT @line = space(@level - 1) + @current
PRINT @line
DELETE FROM #stack
WHERE level = @level
AND item = @current
INSERT #stack
SELECT child, @level + 1
FROM hierarchy
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE
The input parameter (@current) indicates the place in the hierarchy to
start. It also keeps track of the current item in the main loop.
The two local variables used are @level, which keeps track of the current
level in the hierarchy, and @line, which is a work area used to construct
the indented line.
The SET NOCOUNT ON statement avoids cluttering up the output with ROWCOUNT
messages from each SELECT.
The temporary table, #stack, is created and primed with the item identifier
of the starting point in the hierarchy, and @level is set to match. The
level column in #stack allows the same item to appear at multiple levels in
the database. Although this situation does not apply to the geographic data
in the example, it can apply in other examples.
In this example, when @level is greater than 0, the procedure follows
several steps:
If there are any items in the stack at the current level (@level), the
procedure chooses one and calls it @current.
Indents the item @level spaces, and then prints the item.
Deletes the item from the stack so it won't be processed again, and then
adds all its child items to the stack at the next level (@level + 1). This
is the only place where the hierarchy table (#stack) is used.
----------------------------------------------------------------------------
----
Note With a conventional programming language, you would have to find each
child item and add it to the stack individually. With Transact-SQL, you can
find all child items and add them with a single statement, avoiding another
nested loop.
----------------------------------------------------------------------------
----
If there are child items (IF @@ROWCOUNT > 0), descends one level to process
them (@level = @level + 1); otherwise, continues processing at the current
level.
Finally, if there are no items on the stack awaiting processing at the
current level, goes back up one level to see if there are any awaiting
processing at the previous level (@level = @level - 1). When there is no
previous level, the expansion is complete.
-----Original Message-----
From: Kay Smoljak [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 11, 2001 8:43 AM
To: CF-Talk
Subject: 3 selects related and nightmare trees
-- Spamex - Forums Now Available http://forums.spamex.com/forums/
-- Replies will be sent to [EMAIL PROTECTED]
-- Additional Info: http://www.spamex.com/i/?v=52
Hi all,
I posted this on the HouseOfFusion SQL list but haven't had any bites...
Can anyone help?
I have a "limited tree" kind of deal going with a table of categories -
the structure is like so:
pkCategories category_name fkParentCategory
To save it from turning into absolute hell, there can only be three
categories deep - so a category can have a subcategory which can have
subcategories, but that's as far as it goes.
I want to use Nate Weiss's custom tag "three selects related". To use it
I need to create a query object with the following structure:
grandparentID grandparentname parentID parentname childID
childname
I have no idea how to write such a beast. I started out with:
SELECT A.pkCategories, A.category_name, A.fkParentCategory,
( SELECT B.pkCategories, B.category_name,
B.fkParentCategory
FROM Categories B
WHERE B.pkCategories = A.fkParentCategory
)
FROM Categories A
But I'm not really sure what I'm doing or where I'm going :)
If it's not possible with SQL, I could use CF's query functions to
create what I need. That requires a whole rethink though.
Any help greatly appreciated!
Kay.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
