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.
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
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)
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.
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)
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.
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?
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 [COLOR="Blue"]Location_1.sys_whr_id[/COLOR]
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[COLOR="blue"] Location_2.sys_whr_id[/COLOR]
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”
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.