March 5, 2002 at 10:17 pm
I wasn't sure what to name this but essentially what I want to do is:
I have set up a standard tree/menu table that provides navigation to my website. The fields are
TABLE t_Menu
t_Menu_pk int NOT NULL
MenuItem varchar(30) NOT NULL
ParentId int NULL
Each toplevel menu has a parentID of NULL. Each subsequent menu level has a the ID of their parent.
My question is: If a user selects a menu item that is a few levels deep, is there a way to construct a select statement that will return a recordset that would contain the related menu items all the way back to the root, ala Yahoo style navigational hints.? I am currently doing this all in ASP code using 1 query to the database to retrieve all menu items and then figuring out the path back to the root. Any ideas?
Nerds are cool
David Petersen
dipetersen.com
March 6, 2002 at 4:19 am
See if this does what you want:
DECLARE @MenuItem int, @Navigation varchar(300)
SET @MenuItem = 6
SET @Navigation = ''
WHILE @MenuItem IS NOT NULL
BEGIN
SELECT @Navigation = MenuItem + ',' + @Navigation
FROM t_Menu
WHERE t_Menu_pk = @MenuItem
SELECT @MenuItem = ParentId
FROM t_Menu
WHERE t_Menu_pk = @MenuItem
END
SET @Navigation = LEFT(@Navigation, LEN(@Navigation) - 1)
SELECT @Navigation
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
March 6, 2002 at 5:02 am
Or try this, already put in stored procedure format:
CREATE PROCEDURE ip_GetMenuHier
@t_Menu_pk int --The bottom of the hierarchy.
AS
/* Create a local temp table to store our output until ready. */
CREATE TABLE #tblTmpMenu (
[IDx] [int] NOT NULL,
[t_Menu_pk] [int] NOT NULL,
[MenuItem] [varchar] (30) NOT NULL
)
DECLARE @IDx int -- This variable will be our primary key so the lower the number the bottom of the hierarchy, the higher is the top of the hierarchy.
SET @IDx = 1 --Set initial value to 1
WHILE @t_Menu_pk IS NOT NULL --Keep going thru until we have exhasted back to the highest level.
BEGIN
/* Insert the data for the level into the local temp table. */
INSERT INTO #tblTmpMenu (IDx, t_Menu_pk, MenuItem) SELECT @IDx, t_Menu_pk, MenuItem FROM t_Menu WHERE t_Menu_pk = @t_Menu_pk
/*
* Get the next level ups id for next loop, also case statement is to insure against accidents
* of equal id to parent itself or already existing thus sending us along same path,
* you may want to add checks for other possibilities otherwise you will end up in a loop.
*/
SELECT @t_Menu_pk = (CASE WHEN @t_Menu_pk = ParentId THEN NULL WHEN @t_Menu_pk IN (SELECT t_Menu_pk FROM #tblTmpMenu) THEN NULL ELSE ParentId END) from t_Menu WHERE t_Menu_pk = @t_Menu_pk
SET @IDx = @IDx + 1 --Increment our key.
END
SELECT IDx, t_Menu_pk, MenuItem FROM #tblTmpMenu --Get out data output, keep in mind Item 1 is the current level, 2 is 1s parent, etc. , and the max item is the highest level.
DROP TABLE #tblTmpMenu --Drop the local temp table to free memory ASAP.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply