November 7, 2010 at 7:06 am
wht will be the query to find all the menu available in menu id 1?... any help?
-- master table
---------------------------
menu id menuname
1 file
---------------------------
-- child table
menu id menu name submenu
2. add 1
3 edit 1
4 add-new 2
5 add_old 2
November 7, 2010 at 1:14 pm
MonsterRocks (11/7/2010)
wht will be the query to find all the menu available in menu id 1?... any help?-- master table
---------------------------
menu id menuname
1 file
---------------------------
-- child table
menu id menu name submenu
2. add 1
3 edit 1
4 add-new 2
5 add_old 2
Please see the first link in my sig for properly setting up a question. It will help tremendously.
The request and the data don't make sense, as there is no menuID = 1 in the child table.
This question, assuming you want where submenu = 1, is very simple. Is this a homework question?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 7, 2010 at 2:25 pm
Hi there,
I agree with Craig, the test data doesn't make sense.
I'm assuming that submenuid corresponds to menuid and then the query would look something like below.
-- Create testdata
DECLARE @MasterTable TABLE (MenuID INT PRIMARY KEY CLUSTERED, MenuName VARCHAR(30))
INSERT @MasterTable (MenuID, MenuName)
VALUES (1, 'File')
DECLARE @ChildTable TABLE (MenuID INT, MenuName VARCHAR(30), SubMenuID INT)
INSERT @ChildTable (MenuID, MenuName, SubMenuID)
VALUES (2, 'Add', 1)
INSERT @ChildTable (MenuID, MenuName, SubMenuID)
VALUES (3, 'Edit', 1)
INSERT @ChildTable (MenuID, MenuName, SubMenuID)
VALUES (4, 'Add-New', 2)
INSERT @ChildTable (MenuID, MenuName, SubMenuID)
VALUES (5, 'Add-old', 2)
SELECT * FROM @ChildTable
-- Get hierarchy
DECLARE @MenuID INT
SET @MenuID = 1 -- The top menu you want sub-menus for
;WITH Hierarchy(MenuID, SubMenuID, MenuName, [Level])
AS
(
SELECT
MenuID,
SubMenuID,
MenuName,
2
FROM
@ChildTable
WHERE
SubMenuID = @MenuID
UNION ALL
SELECT
CT.MenuID,
CT.SubMenuID,
CT.MenuName,
TH.[Level] + 1
FROM
@ChildTable CT
INNER JOIN Hierarchy TH ON TH.MenuID = CT.SubMenuID
)
SELECT * FROM Hierarchy
ORDER BY [Level]
Andreas Goldman
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply