Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Unanswered: Trying to eliminate children based on hierarchy string

    Let's say I have a table with the following data...

    ID ParentID Hierarchy
    1 0 'Company A>'
    2 1 'Company A>file1>'
    3 1 'Company A>file2>'
    4 0 'Company B>Division X>'
    5 8 'Company B>Division X>Plant Y>Shift 1>'
    6 5 'Company B>Division X>Plant Y>Shift 1>file1>'


    I need a query that will eliminate every row that has a hierarchy which is a descendent of of another's hierarchy.
    So what I need to end up with is...

    1 0 'Company A>'
    4 0 'Company B>Division X>'

    I then need to join this result set to include another table's Hierarchy column WHERE Hierarchy LIKE 'Company A>%' OR Hierarchy LIKE 'Company B>Division X>%'

    I don't want to pay the cost of using a cursor. Hopefully someone can help me out with this one.

    Thanks in advance.
    Joe

  2. #2
    Join Date
    Dec 2003
    Posts
    17
    What exactely are you trying to do?

    To obtain the root node of the heirarchy select where ParentID = 0???

    If you only need to find the child of the root node then use a subquery joining to the same table.

  3. #3
    Join Date
    Dec 2003
    Posts
    7
    Each record represents a selected node(folder) in a hierarchy. If the folder is selected (with a checkbox) then I need to retrieve all data (files and subfolders, and subfolders' subfolders, etc..) from another table WHERE any data item's hierarchy is LIKE '<root hierarchy>%' for all of the root hierarchies. Many different folders can be selected(checked) anywhere in the hierarchy.
    Right now I am using a recursive function in my ASP page to dynamically build an OR string in my query.
    "..WHERE hierarchy LIKE 'A>%' OR hierarchy LIKE 'B>%' " etc...
    I'm strying to spped things up by keeping it all in the DB.

    I apologize if I haven't made myself clear...
    Thanks again.

Posting Permissions

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