MSSQL - Querying for Children of a Parent

[colboy]

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

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

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

[transio]

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.

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

[colboy]

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

[StarLion]

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.

[colboy]

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

[r937]

colboy, how many (maximum) levels are there?

[colboy]

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

[r937]

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)

[StarLion]

(I swear i'm not trying to hijack the thread)

(but pretty tricky to manage for inserts, updates, and deletes)

In what way?

[colboy]

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

[r937]

Storing Hierarchical Data in a Database Article » SitePoint

[StarLion]

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.

[colboy]

Thanks guys. So I'm left with

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

[r937]

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...

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"

[colboy]

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

[wwb_99]

One other option: for_xml_auto. Instamagically makes heirarchical lists based on relationships.