December 11, 2009 at 5:23 am
Hi all,
i had a requirement regarding b-tree .
DECLARE @tbl TABLE (Id INT , [Name] char(2), Lft INT , Rit INT , ParentId INT )
INSERT @tbl SELECT 1,'n1',2,3,0
INSERT @tbl SELECT 2,'n2',4,5,1
INSERT @tbl SELECT 3,'n3',6,7,1
INSERT @tbl SELECT 4,'n4',8,9,2
INSERT @tbl SELECT 5,'n5',10,11,2
INSERT @tbl SELECT 6,'n6',12,13,3
INSERT @tbl SELECT 7,'n7',14,15,3
INSERT @tbl SELECT 8,'n8',16,17,4
SELECT * FROM @tbl
n1 has node 2 TO its left, node3 TO its rigth AND has parentid OF 0 means root node
n2 has node 4 TO its left, node5 TO its RIGHT AND has parentid OF 1
i want COUNT OF the nodes LEFT TO node 1 means '5'(1,2,4,5,8)
I tried with recursive cte but not getting exact output
;WITH cte as
(
SELECT id,name,
,
,topid FROM tree WHERE topid = 0
UNION all
SELECT t1.id, t1.name,t1.[Left],t1.
,t1.TopId
FROM tree t1
JOIN cte c ON c.id = t1.topid AND c.
= t1.Id
)
SELECT * FROM cte
Thanks in advance:-)
December 11, 2009 at 6:55 am
I'll give you a hint since you haven't shown us what you have tried so far, in BOL (Books Online, the SQL Server Help System that you can access by pressing the {f1} function key in SSMS (SQL Server Management Studio)) do some reading on CTE'a and recursive CTE's.
Any questions about these after reading, come back and ask.
December 11, 2009 at 9:04 pm
Hi,
I tried with recursive cte and self join. But not getting required output:-)
December 11, 2009 at 10:15 pm
Post your code, maybe someone will be able to figure out what is wrong.
December 11, 2009 at 10:20 pm
;WITH cte as
(
SELECT id,name,
,
,topid FROM tree WHERE topid = 0
UNION all
SELECT t1.id, t1.name,t1.[Left],t1.
,t1.TopId
FROM tree t1
JOIN cte c ON c.id = t1.topid AND c.
= t1.Id
)
SELECT * FROM cte
December 14, 2009 at 10:07 pm
Hi
Your query is returning ALL the left nodes, starting from node 1. (matches description of what you want)
Based on your example it seems that you want FIRST LEFT NODE to node 1 and ALL nodes underneath.
Node n5 is RIGHT to node N2 and will never be returned because of the and c.Lft = t1.Id
Either example is not good, or description of what you need to achieve is not good.
Please clarify so we can help 🙂
Thanks
December 14, 2009 at 10:41 pm
Additionally,
This (is not very elegant, I know) would return records from your example
DECLARE @tbl TABLE (Id INT , [Name] char(3), Lft INT , Rit INT , ParentId INT )
INSERT @tbl SELECT 1,'n1',2,3,0
INSERT @tbl SELECT 2,'n2',4,5,1
INSERT @tbl SELECT 3,'n3',6,7,1
INSERT @tbl SELECT 4,'n4',8,9,2
INSERT @tbl SELECT 5,'n5',10,11,2
INSERT @tbl SELECT 6,'n6',12,13,3
INSERT @tbl SELECT 7,'n7',14,15,3
INSERT @tbl SELECT 8,'n8',16,17,4
SELECT * FROM @tbl
;WITH cte as
(
SELECT temp.id,temp.name,temp.Lft,temp.Rit,temp.ParentId FROM
(select t2.id,t2.name,t2.lft,t2.rit,t2.parentId from @tbl t
inner join @tbl t2 on t.id=t2.parentId and t.lft=t2.id
where t.parentId=0 ) temp
UNION all
SELECT t1.id, t1.name,t1.Lft,t1.Rit,t1.ParentId
FROM @tbl t1
JOIN cte c ON c.id = t1.ParentId --and c.Lft = t1.Id
)
SELECT * FROM cte
0 node would have to be added manually 🙁
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply