Help Needed

  • 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:-)

  • 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.

  • Hi,

    I tried with recursive cte and self join. But not getting required output:-)

  • Post your code, maybe someone will be able to figure out what is wrong.

  • ;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

  • 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

  • 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