SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2004
    Location
    Ottawa, Canada
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MSSQL - Querying for Children of a Parent

    I have a large table in MSSQL 2005, that is something like

    SystemID
    Name
    ParentID

    The System ID is a unique GUID, the name is the plain text description of the location and the ParentID is the Location under which the Location falls. So one location can be Parent to many children. What I'm wondering, is if it there is an easy way to find out all the children of a parent, baring in mind, that a child can also be the parent of another. A simple example would be

    Code:
    World
     - North America
     - - Canada
     - - - Ontario
     - - - - Nepean
     - - - - - Barrhaven
    For this I've been doing multiple queries, but this doesn't work so well when you have


    Code:
    World
     - North America
     - - Canada
     - - - Ontario             - - - Alberta            - - - British Columbia
     - - - - Nepean           - - - - Calgary         - - - - Vancouver
     - - - - - Barrhaven
    So if I want all the children of Canada, I would get all of the Ontario, Alberta and BC branches, and all their children. I'm probably asking too much of SQL, but is there something to help, or am I better off doing this programatically.

    Thanks

    Colin

  2. #2
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you know the maximum number of levels you want to retrieve, this is simple enough to do in one query.. the only thing you'll have to figure out is how you want your results, and how you'll handle them.

    I think since you're looking for genealogy, you won't want to group your data. Here's a 5-level genealogy query example. I added a "Level" record in the results to let you know what level of item is currently being returned. You can use this to determine how you place the new item in your genealogy structure in the app you build.
    Code SQL:
    SELECT 
        l1.SystemID, l1.Name, 
        l2.SystemID AS SystemID2, l2.Name AS Name2,
        l3.SystemID AS SystemID3, l3.Name AS Name3,
        l4.SystemID AS SystemID4, l4.Name AS Name4,
        l5.SystemID AS SystemID5, l5.Name AS Name5,
        CASE 
            WHEN l5.SystemID IS NOT NULL THEN 5
            WHEN l4.SystemID IS NOT NULL THEN 4
            WHEN l3.SystemID IS NOT NULL THEN 3
            WHEN l2.SystemIID IS NOT NULL THEN 2
            ELSE 1
        END AS Level
    FROM
        Locations AS l1
        LEFT JOIN Locations AS l2 ON l1.SystemID = l2.ParentID
        LEFT JOIN Locations AS l3 ON l1.SystemID = l3.ParentID
        LEFT JOIN Locations AS l4 ON l1.SystemID = l4.ParentID
        LEFT JOIN Locations AS l5 ON l1.SystemID = l5.ParentID
    WHERE l1.ParentID = 6; // (e.g. WHERE 6 = Canada)

    If you want the full tree, you can just specify WHERE l1.ParentID IS NULL

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2004
    Location
    Ottawa, Canada
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that, but rather than the hierarchy, I need to return all the SystemID's that match. I basically have many assets

    SysAssetId
    Name
    SysLocationId (or as in the previous example SysId)

    stored in many locations. I want to be able to say give me all the assets that are in Canada, or Ontario, etc. I'm hoping to end up with one query (the one giving me all the children) I can link to another (the one giving me all the assets)

    Thanks, Colin

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    so you're looking for all possible leaves of the tree such that they belong (at some unspecified and potentially varied depths) to a target node.

    That... is very difficult in my head to do in an Adjacency system. I'm not sure if MSSQL can handle the NSM system efficiently or not, but that would seem to me to be the way to move.

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2004
    Location
    Ottawa, Canada
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had a feeling it was not going to be something simple. Just wanted to see if anyone had done this, before writing my own code to do the same thing.

    I'm constantly surprised by the power of SQL and find my self always trying to push things. It's the only way you learn I suppose.

    Thanks, Colin

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    colboy, how many (maximum) levels are there?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2004
    Location
    Ottawa, Canada
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    colboy, how many (maximum) levels are there?
    Ideally 2 or 3, but as this is for a generic system, could be many more, although I can set an artificial limit and expand it as required.

    Colin

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    a fixed maximum allows you to retrieve what you want from the structure you currently have (adjacency list model, i.e. "parentid") with a single query, although it will have multiple joins, as shown by transio's query

    "could be many more" suggests that you might want to switch to the nested set model, which is a lot easier to query (but pretty tricky to manage for inserts, updates, and deletes)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    (I swear i'm not trying to hijack the thread)
    (but pretty tricky to manage for inserts, updates, and deletes)
    In what way?

  10. #10
    SitePoint Enthusiast
    Join Date
    May 2004
    Location
    Ottawa, Canada
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The former would probably work, at least to solve me initial problem. Can look at the other method in detail later. Do you have an example I can look at and any pointers for good explanations of Nested Set Models.

    Thanks, Colin

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    Sitepoint Blog - Storing Hierarchical Data in a Database. What the blog calls the "Modified Preorder Tree" on page 2 is a Nested Set Model.
    MySQL - Managing Hierarchical Data in MySQL
    While the queries might not be specifically relevant, the article does lay out differences between Adjacency and Nested Set models.

  13. #13
    SitePoint Enthusiast
    Join Date
    May 2004
    Location
    Ottawa, Canada
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys. So I'm left with

    Code:
    SELECT     Location.sys_whr_id AS Level1, Location_1.sys_whr_id AS Level2, Location_2.sys_whr_id AS Level3, Location_3.sys_whr_id AS Level4, 
                          Location_4.sys_whr_id AS Level5, Location_5.sys_whr_id AS Level6, Location_6.sys_whr_id AS Level7, Location_7.sys_whr_id AS Level8, 
                          Location_8.sys_whr_id AS Level9, Location_9.sys_whr_id AS Level10
    FROM         Location LEFT OUTER JOIN
                          Location AS Location_1 ON Location.sys_whr_id = Location_1.sys_parent_id LEFT OUTER JOIN
                          Location AS Location_2 ON Location_1.sys_whr_id = Location_2.sys_parent_id LEFT OUTER JOIN
                          Location AS Location_3 ON Location_2.sys_whr_id = Location_3.sys_parent_id LEFT OUTER JOIN
                          Location AS Location_4 ON Location_3.sys_whr_id = Location_4.sys_parent_id LEFT OUTER JOIN
                          Location AS Location_5 ON Location_4.sys_whr_id = Location_5.sys_parent_id LEFT OUTER JOIN
                          Location AS Location_6 ON Location_5.sys_whr_id = Location_6.sys_parent_id LEFT OUTER JOIN
                          Location AS Location_7 ON Location_6.sys_whr_id = Location_7.sys_parent_id LEFT OUTER JOIN
                          Location AS Location_8 ON Location_7.sys_whr_id = Location_8.sys_parent_id LEFT OUTER JOIN
                          Location AS Location_9 ON Location_8.sys_whr_id = Location_9.sys_parent_id
    WHERE     (Location.sys_whr_id = 'ONTARIO') // But this would really be a GUID
    Which means I have to traverse all the records and fields returned to give me a list of unique ID's and use this as the basis of my next query. Any comments before I put this to bed?

    Colin

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by colboy View Post
    Which means I have to traverse all the records and fields returned to give me a list of unique ID's
    well, they're all going to be unique, aren't they -- otherwise it wouldn't be a hierarchy

    but yeah, you'd have to go across the rows returned and pull off the ids from the various columns

    there is another way to do it, that gives you a much more usable result set, although it's somewhat even more cumbersome...
    Code:
    SELECT Location_1.sys_whr_id 
      FROM Location 
    INNER 
      JOIN Location AS Location_1 
        ON Location_1.sys_parent_id = Location.sys_whr_id
     WHERE Location.sys_whr_id = 'ONTARIO') 
    UNION ALL
    SELECT Location_2.sys_whr_id 
      FROM Location 
    INNER 
      JOIN Location AS Location_1 
        ON Location_1.sys_parent_id = Location.sys_whr_id
    INNER 
      JOIN Location AS Location_2 
        ON Location_2.sys_parent_id = Location_2.sys_whr_id
     WHERE Location.sys_whr_id = 'ONTARIO'
    UNION ALL
    .
    . -- and so on for all levels, using INNER joins
    .
    this returns all nodes in a single column

    you could then push the entire union query into a subquery, and thus "use this as the basis of my next query"
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Enthusiast
    Join Date
    May 2004
    Location
    Ottawa, Canada
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By unique I meant unique within the list I am building. Thanks for the last post, that allows me to skip my list building function. Will look at Nested Set Models as a separate exercise.

    Thanks everyone for all your help.

    Colin

  16. #16
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    One other option: for_xml_auto. Instamagically makes heirarchical lists based on relationships.


Bookmarks

Posting Permissions

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