Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2014
    Posts
    6

    Unanswered: Creating a hierarchy of parent organizations

    Hey all,

    Slightly complicated issue I've tried solving but haven't got anywhere.

    I have hundreds of rows in Table A, each with an Organization ID, Organization Name, and Parent ID.

    There's also an Organization table which contains the Organization mappings (this also has an Organization ID, Organization Name, Parent ID). - The parent ID is basically an Organization ID, referring to the Parent Organization (and this is recursive, so each Parent can have another Parent etc.)

    The problem I have is in Table A. I need to create new columns showing the parent hierarchy starting from the Organization Name in the table.
    This step is quite easy, simply using a JOIN with the Organization table ON Parent ID works pretty well.

    The complication comes here:
    -Every Organization in Table A is the lowest level in its hierarchy (i.e. it doesn't have any children).
    - Every Organization in Table A has a varying amount of parents (i.e. some might have 1 parent who then has another parent, or some might have 1 parent as the BOSS (top most level))

    I need to organise the Table so that each organization can have its parents listed beside (one in each column), and should show NULL if it doesn't have a parent at a certain level. - This bit is difficult to explain, so I've attached an image

    So essentially, regardless of how many parents an organization has, the top most parent (BOSS) should always appear under the same column. (image explains this)

    I'd really appreciate some help, if you need any clarifications let me know!

    Thank you!
    Attached Thumbnails Attached Thumbnails orgtable.png   TableA.png  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2014
    Posts
    6
    Quote Originally Posted by healdem View Post
    Checked

    That example matches my explanation about the normal way of creating this hierarchy (Quoted from my thread: "The problem I have is in Table A. I need to create new columns showing the parent hierarchy starting from the Organization Name in the table.
    This step is quite easy, simply using a JOIN with the Organization table ON Parent ID works pretty well.")

    Referring to that example, what I need is to have the highest level in the same column. (i.e. the root 'Animal' should ONLY appear in up3_name. If the leaf doesnt have any other parents, then up2_name and up1_name should just be NULL)

    Basically the idea is to be able to GROUP BY a certain organization (but obviously this cant be done easily if the organization you want to group by is mentioned in multiple columns)

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Google "nested Sets model"

Posting Permissions

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