terriblizzard
New Member
This might seem a bit silly, but I want to try and solve this query in one go.I have a table in a SQL database that links back onto itself. It has an Organization ID and a Parent Organization ID. Top level orgs have a parent ID of 0, and each org at a different level has a parent org of one of the orgs above it. Level of the org used to be determined by another field in the table called org Level. However, now the level is determined by how far down the tree the org is. Such that if an org had a parent that had another parent, that first org would be at level 3, it's parent at level 2, and it's grandparent at level 1.I understand how I want my SQL to work, but the problem is creating a generic SQL statement that will return me the orgs and their levels. I decided to try and use XML to do this, but this might not be the best idea. Let's say I have an org structure like this.\[code\]-Top Org 1--Second Org 1--Second Org 2-Top Org 2--Second Org 3--Second Org 4-Top Org 3--Second Org 5--Second Org 6\[/code\]When done, I want to query an XML that looks like this:\[code\]<Organizations> <Lvl1 OrgID="1" Name="Top Level 1" Index="1"> <Lvl2 OrgID="5" Name="Second Level 1" Index="1" /> <Lvl2 OrgID="6" Name="Second Level 2" Index="2" /> </Lvl1> <Lvl1 OrgID="3" Name="Top Level 2" Index="2"> <Lvl2 OrgID="7" Name="Second Level 3" Index="1" /> <Lvl2 OrgID="8" Name="Second Level 4" Index="2" /> </Lvl1> <Lvl1 OrgID="4" Name="Top Level 3" Index="3"> <Lvl2 OrgID="9" Name="Second Level 5" Index="1" /> <Lvl2 OrgID="10" Name="Second Level 6" Index="2" /> </Lvl1></Organizations>\[/code\]I have figured out how I want to do this normally, if I know how many levels I want to query for. If I want to query the first and second levels, I do this:\[code\]SELECT Lvl1.Org_ID [@OrgID] ,Lvl1.Org_Name [@Name] ,Lvl1.Org_SortID [@Index] ,( SELECT Lvl2.Org_ID [@OrgID] ,Lvl2.Org_Name [@Name] ,Lvl2.Org_SortID [@Index] FROM Organizations Lvl2 WHERE Lvl2.Org_ParentID = Lvl1.Org_ID GROUP BY Lvl2.Org_ID, Lvl2.Org_Name, Lvl2.Org_SortID ORDER BY Lvl2.Org_SortID FOR XML PATH('Lvl2'), TYPE )FROM Organizations Lvl1INNER JOIN Organizations Child1 ON Lvl1.Org_ID = Child1.Org_ParentIDWHERE Lvl1.Org_ParentID = 0GROUP BY Lvl1.Org_ID, Lvl1.Org_Name, Lvl1.Org_SortIDORDER BY Lvl1.Org_SortIDFOR XML PATH('Lvl1'), ROOT('Organizations')\[/code\]But let's pretend that I don't know how many levels I'm going to have. I might have only two levels, I might have 15 levels. And not every org could have the same number of levels. What I want to find out is if there's a way to recursively look at each record as it comes in and determine if it has any children under it. If so, create new XML tags for those children and drill down one level, then check each child and see if THEY have children. Ect.Maybe there's not a good way of doing this, or many it's a huge memory and time sink to make SQL do something like this. But if it's not, let me know that it's possible and show me an example. Thanks!