Treeview CTE Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Treeview CTE Query

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-06-10 : 09:45:12

I have written cte for tree view structured table

Columns: ID, ParentID, Name
Below query returns all data for passed parent upto n level



;WITH OrderLinksCTE
AS
(
	SELECT Parentid = ParentMenuID, MenuID, MenuName, LVL = 1
	FROM   tbl_Menus 
	WHERE  ParentMenuID = 1
	
	UNION ALL

	SELECT SML.ParentMenuID, SML.MenuID, SML.MenuName, LVL = c.LVL + 1
	FROM   OrderLinksCTE c INNER JOIN tbl_Menus SML
						ON SML.ParentMenuID = c.MenuID
)
SELECT *
FROM OrderLinksCTE 
ORDER BY LVL

But I will pass one by one call to sql and will pass one parentid at a time
I need last column LEVEL -- If I pass 3rd level parent id then related LEVEL should come in return in list which returns at time of executing of all records

Current output


ID  parentid  name   level
2     1        a       1     
3     1        b       1     
4     3        c       2     
5     3        d       2     
6     3        e       2     

Required output
If i pass parent id 3 then level 2 should come in list.. currently above query returns 1 level

Please help

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-10 : 09:54:17

Not sure what you want.

SELECT *
FROM OrderLinksCTE
where Parentid = @passedid

That will give just the level 2 items if @passedid = 3

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-06-10 : 09:56:57

Yes that i know but cte will looped for all records and then at last it will filter with our parent id..

Isn't any other way that do without loop other records..

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-10 : 10:10:03

well you need to loop to find the level of the id passed but you can loop up rather than down
something like

;WITH OrderLinksCTE
AS
(
SELECT top 1 ParentMenuID from tbl_Menus where MenuID = @passedid
union all
select top 1 MenuID = t.ParentMenuID
FROM tbl_Menus t
join OrderLinksCTE c
on t.MenuID = c.ParentMenuID
)
SELECT *, level = (select COUNT(*) from OrderLinksCTE) -- maybe +1
FROM tbl_Menus
where MenuID = @passedid

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-06-12 : 10:16:40

It is showing error..

TOP operator is not allowed in the recursive part of a recursive common table expression 'OrderLinksCTE'.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-13 : 07:38:00

declare @tbl_Menus table (MenuID int, ParentMenuID int)
insert @tbl_Menus values
(1,0) ,
(2,1) ,
(3,1) ,
(4,3) ,
(5,3) ,
(6,3)

declare @passedid int = 3

;WITH OrderLinksCTE
AS
(
SELECT ParentMenuID = max(ParentMenuID), level = 1
from @tbl_Menus where MenuID = @passedid
union all
select ParentMenuID = t.ParentMenuID, level = level+1
FROM @tbl_Menus t
join OrderLinksCTE c
on t.MenuID = c.ParentMenuID
)
SELECT *, level = (select MAX(level) from OrderLinksCTE)
FROM @tbl_Menus
where ParentMenuID = @passedid

MenuID ParentMenuID level
----------- ------------ -----------
4 3 2
5 3 2
6 3 2

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tafinami
Starting Member

1 Post

Posted - 2014-07-01 : 05:00:06

Tree Type Value With Leaf Id [url]http://www.44data.com/2014/06/query-for-showing-tree-type-value-with.html[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources