Post reply

Recursive SQL Query

  • August 12, 2009 at 1:55 pm

    #137932

    I've been looking at many SQL Server examples using CTE and recursion but I haven't been able to get exactly what I need so I'm posting here for some help.

    This is a fairly typical example of hierarchy data in a SQL table that I want to query against and return in a sorted result set.

    Given this sample data:

    NodeIDParentNodeIDSiblingOrder

    ==============================

    145 null 1

    345 145 2

    788 345 1

    577 145 1

    311 788 2

    224 345 2

    741 788 1

    The data would appear in a tree view as:

    145

    ---577

    ---345

    ------788

    ---------741

    ---------311

    ------224

    I would like it to appear in this order in a query result set:

    145

    577

    345

    788

    741

    311

    224

    Any help would be appreciated.

  • August 12, 2009 at 2:02 pm

    #1038269

    Look at the sample code in BOL under Recursive CTE's. You can access BOL from SSMS by pressing the {f1} function key.

    You can also check out this post as well.

  • August 12, 2009 at 2:29 pm

    #1038298

    I looked at the sample code in BOL and the query runs but is not returning the records in the sequence I want. It returns the first level 0 seed record, then all the level 1 records then it's fairly random after that.

  • August 12, 2009 at 2:52 pm

    #1038314

    kgoyena (8/12/2009)

    I looked at the sample code in BOL and the query runs but is not returning the records in the sequence I want. It returns the first level 0 seed record, then all the level 1 records then it's fairly random after that.

    Well, post your code so we can see what you did. Can't help if we don't know what you have tried.

  • August 12, 2009 at 3:09 pm

    #1038332

    WITH DirectReports(parentNode, childNode, LEVEL ) AS

    (

    SELECT e.parentTocNodeGuid, e.tocNodeGuid, 0 AS LEVEL

    FROM TocNodes AS e

    WHERE e.parentTocNodeGuid IS NULL AND e.tocNodeGuid = '9a9b8bae-849d-4ced-8427-36d9b816f6c1'

    UNION ALL

    SELECT e.parentTocNodeGuid, e.tocNodeGuid, LEVEL + 1

    FROM TocNodes AS e INNER JOIN

    DirectReports AS d ON e.parentTocNodeGuid = d .childNode

    )

    SELECT parentNode, childNode, LEVEL FROM DirectReports

    Results look like this:

    NULL9a9b8bae-849d-4ced-8427-36d9b816f6c10

    9a9b8bae-849d-4ced-8427-36d9b816f6c174998bc2-71b4-4781-a0a6-c9a25e267d071

    9a9b8bae-849d-4ced-8427-36d9b816f6c196da2def-8cbe-4bf8-bc49-d2a765c868031

    9a9b8bae-849d-4ced-8427-36d9b816f6c19c6e4dd4-3877-4100-a8e2-5c60c5e19bb01

    9a9b8bae-849d-4ced-8427-36d9b816f6c1ed6131df-efca-4337-9594-583e19ca3b761

    9a9b8bae-849d-4ced-8427-36d9b816f6c1ca56478d-091a-4705-942f-8928642a59a61

    9a9b8bae-849d-4ced-8427-36d9b816f6c197504faf-7bdb-42ff-9903-7c5793265a6b1

    9a9b8bae-849d-4ced-8427-36d9b816f6c1c52a765e-5a62-4c28-9e3f-d5ed334cadf61

    c52a765e-5a62-4c28-9e3f-d5ed334cadf656b0f52e-61c0-4b85-99cb-911ea7b8bafe2

    c52a765e-5a62-4c28-9e3f-d5ed334cadf673ee2e78-4b34-4683-b662-7e0f8582ffc32

    73ee2e78-4b34-4683-b662-7e0f8582ffc371fdbf63-9101-44e6-a9db-a75acc6226f33

    73ee2e78-4b34-4683-b662-7e0f8582ffc3dce3dc46-2581-472b-9f75-54149063c8813

    73ee2e78-4b34-4683-b662-7e0f8582ffc3bfdeda55-46fc-4b53-b4cd-c71838ef4b413

    73ee2e78-4b34-4683-b662-7e0f8582ffc364df3562-34df-4fb3-8706-e130a0732f7e3

    73ee2e78-4b34-4683-b662-7e0f8582ffc3245bee85-a541-49e1-9975-a6f95595a97e3

    73ee2e78-4b34-4683-b662-7e0f8582ffc3e5e9d65e-a4ff-405c-9a1d-a1135523e91c3

    e5e9d65e-a4ff-405c-9a1d-a1135523e91c2d5b4cf8-cc2a-4255-9323-d9f42eb26a564

    e5e9d65e-a4ff-405c-9a1d-a1135523e91c68d0c4d8-9532-4812-9208-b3c886cde6824

    e5e9d65e-a4ff-405c-9a1d-a1135523e91c453d700b-a3a9-49b7-991e-2af40e50efef4

    453d700b-a3a9-49b7-991e-2af40e50efef2a0e7b2c-aef0-4a84-9ca5-804249f005345

  • August 12, 2009 at 3:16 pm

    #1038335

    kgoyena (8/12/2009)

    WITH DirectReports(parentNode, childNode, LEVEL ) AS

    (

    SELECT e.parentTocNodeGuid, e.tocNodeGuid, 0 AS LEVEL

    FROM TocNodes AS e

    WHERE e.parentTocNodeGuid IS NULL AND e.tocNodeGuid = '9a9b8bae-849d-4ced-8427-36d9b816f6c1'

    UNION ALL

    SELECT e.parentTocNodeGuid, e.tocNodeGuid, LEVEL + 1

    FROM TocNodes AS e INNER JOIN

    DirectReports AS d ON e.parentTocNodeGuid = d .childNode

    )

    SELECT parentNode, childNode, LEVEL FROM DirectReports

    Results look like this:

    NULL9a9b8bae-849d-4ced-8427-36d9b816f6c10

    9a9b8bae-849d-4ced-8427-36d9b816f6c174998bc2-71b4-4781-a0a6-c9a25e267d071

    9a9b8bae-849d-4ced-8427-36d9b816f6c196da2def-8cbe-4bf8-bc49-d2a765c868031

    9a9b8bae-849d-4ced-8427-36d9b816f6c19c6e4dd4-3877-4100-a8e2-5c60c5e19bb01

    9a9b8bae-849d-4ced-8427-36d9b816f6c1ed6131df-efca-4337-9594-583e19ca3b761

    9a9b8bae-849d-4ced-8427-36d9b816f6c1ca56478d-091a-4705-942f-8928642a59a61

    9a9b8bae-849d-4ced-8427-36d9b816f6c197504faf-7bdb-42ff-9903-7c5793265a6b1

    9a9b8bae-849d-4ced-8427-36d9b816f6c1c52a765e-5a62-4c28-9e3f-d5ed334cadf61

    c52a765e-5a62-4c28-9e3f-d5ed334cadf656b0f52e-61c0-4b85-99cb-911ea7b8bafe2

    c52a765e-5a62-4c28-9e3f-d5ed334cadf673ee2e78-4b34-4683-b662-7e0f8582ffc32

    73ee2e78-4b34-4683-b662-7e0f8582ffc371fdbf63-9101-44e6-a9db-a75acc6226f33

    73ee2e78-4b34-4683-b662-7e0f8582ffc3dce3dc46-2581-472b-9f75-54149063c8813

    73ee2e78-4b34-4683-b662-7e0f8582ffc3bfdeda55-46fc-4b53-b4cd-c71838ef4b413

    73ee2e78-4b34-4683-b662-7e0f8582ffc364df3562-34df-4fb3-8706-e130a0732f7e3

    73ee2e78-4b34-4683-b662-7e0f8582ffc3245bee85-a541-49e1-9975-a6f95595a97e3

    73ee2e78-4b34-4683-b662-7e0f8582ffc3e5e9d65e-a4ff-405c-9a1d-a1135523e91c3

    e5e9d65e-a4ff-405c-9a1d-a1135523e91c2d5b4cf8-cc2a-4255-9323-d9f42eb26a564

    e5e9d65e-a4ff-405c-9a1d-a1135523e91c68d0c4d8-9532-4812-9208-b3c886cde6824

    e5e9d65e-a4ff-405c-9a1d-a1135523e91c453d700b-a3a9-49b7-991e-2af40e50efef4

    453d700b-a3a9-49b7-991e-2af40e50efef2a0e7b2c-aef0-4a84-9ca5-804249f005345

    Probably have been better to use the same data you posted in your initial post. I can't really make heads or tells from the data above.

  • August 12, 2009 at 3:22 pm

    #1038340

    The data from my first post was just sample data.

    What you can see from the actual results is that all the level 1 records are coming back first but each of them have children and grandchildren so I want to see a parent with all it's descendents before the parent's siblings are show with there descendents.

  • August 12, 2009 at 3:27 pm

    #1038345

    Maybe, but short integers are a heck of a lot easier to read the GUIDs.

  • August 12, 2009 at 3:52 pm

    #1038367

    i got my answer on MSDN. it involes build a full nodepath value using the siblingorder as such:

    WITH Yak(NodeID, ParentNodeID, NodePath) AS (SELECT TOP (100) PERCENT tocNodeGuid, parentTocNodeGuid, CAST(STR(siblingOrder, 8) + '/' AS VARCHAR(MAX))

    AS Expr1

    FROM TocNodes

    WHERE (parentTocNodeGuid IS NULL) AND (tocNodeGuid = '9a9b8bae-849d-4ced-8427-36d9b816f6c1')

    ORDER BY siblingOrder

    UNION ALL

    SELECT TOP (100) PERCENT s.tocNodeGuid, s.parentTocNodeGuid, y.NodePath + CAST(STR(s.siblingOrder, 8)

    + '/' AS VARCHAR(MAX)) AS Expr1

    FROM TocNodes AS s INNER JOIN

    Yak AS y ON y.NodeID = s.parentTocNodeGuid

    ORDER BY siblingOrder)

    SELECT Yak_1.NodeID, Yak_1.ParentNodeID, Yak_1.NodePath, CASE tocTitle WHEN '' THEN topicTitle ELSE tocTitle END AS Expr1

    FROM Yak AS Yak_1 INNER JOIN

    Topics AS t ON t.topicGuid = Yak_1.NodeID

    ORDER BY Yak_1.NodePath

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply