March 19, 2009 at 1:35 pm
I need to create a procedure in which I pass in a menu_id. From that menu_id, I want the results to display in a hierarchical fashion - top level down all of the parents.
my_table
[menu_id] [numeric](9, 0) IDENTITY(1,1) NOT NULL,
[menu_text] [varchar](100) COLLATE SQL_Latin1_General_CP437_BIN NULL,
[parent_id] [numeric](9, 0) NULL
So for example, if I pass in '5', then '4' might be a parent of '5'. So I would have:
menu_id......parent_id
5...............4
However, '4' may have a parent_id of '3'. So I would need:
menu_id......parent_id
4...............3
5...............4
Can I do this in one statement w/out using loops?
March 19, 2009 at 1:37 pm
Look up "Common Table Expressions" (also called CTEs) in Books Online. It has a good example of how to use one to resolve hierarchies like this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 19, 2009 at 1:53 pm
Here's the link:
http://msdn.microsoft.com/en-us/library/ms186243.aspx
An example (quickly generecised from my code base):
WITH TheGroups as
(select i.item_id, i.group_id, g.group_name, g.department_id
from user_group_item i
inner join user_group g
on i.group_id = g.group_id
inner join split_csv(@v_user_ids,',') ids
on ids.token = i.item_id
where
g.delete_flag = 'n'
UNION ALL
--Recursive part of query
select i.item_id, s.group_id, g.group_name, g.department_id
from user_subgroup s
inner join TheGroups i
on s.sub_group_id = i.group_id
inner join user_group g
on s.group_id = g.group_id
where
g.delete_flag = 'n'
)
SELECT DISTINCT group_id, group_name, item_id
FROM TheGroups
where @v_department_id = -1 or department_id = @v_department_id;
March 21, 2009 at 1:43 am
Hi Rog,
Just go through the link
http://www.sqlservercentral.com/Forums/Topic675808-338-1.aspx
I have solved the similar problem.
Regards
Rao
March 21, 2009 at 5:33 pm
There are three problems with recursive CTE's being used to supposedly resolve hierarchies...
1. Recursive CTE's are slower than cursors and a fair bit slower than a good "Fire Hose" cursor or a well written While Loop. Recursive CTE's are what I classify as RBAR on steriods and frequently result in X2 rows (yep... hidden Cartesian joins) being produced behind the scenes.
2. Because of the nature of how CTE's can be made to recurse, they list everything in a level before proceding to the next level rather than building the data in a "tree" fashion. That may or may not be a problem depending on what your requirements are, of course.
3. You're recalculating the downline every time. That doesn't make sense if the hierarchy will only change sporatically over a period of hours or days. Techniques like the "nest set mode" are much more efficient at doing such a thing.
There're a lot of ways to do the "downline" search you're asking for. I'd suggest the a recursive CTE is both the easiest and the slowest way to do it and, if it were me, I'd probably look for another way.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2009 at 7:55 am
use old way to get the parent id
CREATE TABLE #my_table (
[menu_id] [NUMERIC](9,0) IDENTITY ( 1 , 1 ) NOT NULL,
[menu_text] [VARCHAR](100) COLLATE sql_latin1_general_cp437_bin NULL,
[parent_id] [NUMERIC](9,0) NULL)
INSERT INTO #my_table
(menu_text,
parent_id)
SELECT 'A',
NULL
UNION
SELECT 'B',
1
UNION
SELECT 'C',
2
UNION
SELECT 'D',
3
SELECT t.menu_id,
CASE
WHEN t.parent_id IS NULL THEN t.menu_id
ELSE t.parent_id
END parent_id
FROM #my_table t
LEFT OUTER JOIN #my_table tt
ON t.menu_id = tt.parent_id
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply