Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    53

    Talking Unanswered: recursive cursor

    Hi,

    I have 2 tables

    tblparent

    parent_term_id term_id
    -------------- -----------
    1 2
    2 3

    tblname

    id name
    ----- ------------------------------------------
    1 My top parent node
    2 My second node
    3 my child node


    If I do a search for say 'my child node' I need to display where 'my child node' is in relation to the hierarchy. i.e i need to show it's parent and if that has a parent I need to show its parent etc... and continue until there are no more parents left

    So using the table details if i search for 'my child node'

    I need to display this :
    My top parent node -> My second node - > my child node

    The id for 'My top parent node' doesn't exist in tblparent because it is the top parent

    Can anybody help with doing this

    Thanks in advance

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Forget recursion. It sucks in SQL. Its generally better to use a temporary table or table variable for accumulating the results you want in hierarchical algorithms.

    And just forget cursors too, while you are at it.

    I am unclear about the result set you want.

    "My top parent node -> My second node - > my child node" implies a single output record. Are you looking for a delimited string result? Simpler and more common is to return a multi-record list of nodes:

    1 My top parent node
    2 My second node
    3 my child node

    How about a few more details on the problem you are trying to solve?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2004
    Posts
    53
    I have a form which has a search input box, a search button and a hierarchical tree structure

    If a user types in a search term in an input box e.g. my child node, and clicks the search button, I then go off to sql to get the results

    When the results come back to the front-end i need to display where the search term is in the tree strucure.

    The user types in 'my child node', when the results are displayed is has to be like this 'My top parent node->My second node-> my child node' - like a crumb trail



    Thanks in advance
    Last edited by plork123; 09-28-04 at 11:55.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That's why I love Yukon! Typical BOM case, and a very elegant solution!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First, you do not need two tables. That just confused the situation. A node can only have one parent, right? So use a structure like this:

    NodeID ParentID NodeName
    1 TopNode
    2 1 SecondNode
    3 2 ChildNode

    Then use a function like this to get any node's lineage:

    create function GetNodeLineage(@NodeID as int)
    returns varchar(500) as

    begin

    declare @ResultTable as table
    (ParentID int, Lineage varchar(500))

    --Get the initial values
    insert into @ResultTable (ParentID, Lineage)
    select ParentID, NodeName
    from YourTable where NodeID = @NodeID

    While @@Rowcount > 0
    Update ResultTable
    set ParentID = YourTable.ParentID,
    Lineage = YourTable.NodeName + ">" + Lineage
    from @ResultTable ResultTable
    inner join YourTable on ResultTable.ParentID = YourTable.NodeID

    Return (select Max(Lineage) frp, @ResultTable)
    end

    You'll need to check my syntax, as I have been working in Oracle lately and my TSQL is a bit rusty, but you should be able to see the algorithm used.
    Make sure your relationships are correct, or you could find yourself in an endless loop!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmm, that's interesting...No master table for node names? So should I change my signature to "Help me BLindman"???...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    With this schema, the Master table is retained, but with the addition of the ParentID field. It is the subtable that is eliminated. I only use those if there is a many-to-many relationship.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jan 2004
    Posts
    53
    thanks for the advice

    Unfortunately this is an application (with a sql back-end) that was bought and i'm given the task of doing a front-end to it, the table structure is beyond my control in a way. But i can join the 2 tables together to give your example a go

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •