Get leaf node in a tree built using HierarchyId in SQL Server

Question

I am using the HierarchyId data type in SQL Server. I must write some queries.

The table consists of countries, regions and provinces (or districts).

I must write two queries:

1. I must return all nearest descendants.
2. I must return all leafs.

I resolved the first one query, but not the second:

SELECT * 
INTO #parents 
FROM ado.country_subdivisions parents 
WHERE  isocode = 'ES' 

SELECT * 
FROM ado.country_subdivisions cs
WHERE IsoCode IN (SELECT cs.IsoCode
                  FROM #parents p
                  WHERE cs.Level.IsDescendantOf(p.Level) = 1
                    AND p.CountryISOAlpha2Code = cs.CountryISOAlpha2Code
                    AND (cs.Level.GetLevel() = p.Level.GetLevel() + 1)
                 )   

I get this

I think I must change only this to resolve the second query

AND (cs.Level.GetLevel() = p.Level.GetLevel() + 1)

with something like

(SELECT MAX(leaf.Level.GetLevel()) FROM ado.country_subdivisions leaf WHERE leaf.Level.IsDescendantOf(cs.Level) = 1)

But I get only provinces... But I have some regions that does not have provinces.. so I expect to get Provinces and Regions where provinces are not available.

Here the exaple... It seems in the exaple works, so I do not understand what is the problem locally :(

http://sqlfiddle.com/#!18/46cbd/1

Any ideas?

Thank you

Answer 1

On the assumption that you mean "leaf" to be "a node in the tree without any descendants", you'll need something like this:

select cs.*,
    cs.[Level].ToString()
from dbo.country_subdivisions as cs
join dbo.parent as p
    on cs.[Level].IsDescendantOf(p.[Level]) = 1
    and p.CountryISOAlpha2Code = cs.CountryISOAlpha2Code
where not exists (
   select 1
   from Dbo.country_subdivisions as child
   where child.[Level].IsDescendantOf(cs.[Level]) = 1
      and child.[Level] <> cs.[Level]
)
order by cs.[Level];