Get Lowest level in tree created with hierarchyId that respect some conditions

Question

I have created a hierarchy table in my SQL Server. I have a column hierarchyId. Each level of hierarchy represent a geographical/political level of a country:

• Countries
• Regions
• Provinces

For each row I can fill some boundaries or not. To simplify I replace in the example the geometry column with a bit column.

I need to get lowest level that has boundaries filled. If at least one child has boundaries, surely also parent has boundaries.

I make an example:

For example, I have that tree. In my query I should get green and red areas. In this moment I get only green areas.. so, I should get:

• Valle d'aosta because is the lowest level and it has boundaries (and that is OK);
• Milano and Brescia because they are the lowest level with boundaries. I should not get Bergamo because it has no boundaries, but I should get Lombardia instead of Bergamo;
• Italy because both Piemonte and Torino have no boundaries;
• Lazio because Roma has no boundaries.

My query is partially correct.. I get all lowest levels. But I do not get the minimum high level that respect my condition..

I share a link with the example: http://sqlfiddle.com/#!18/878577/1

Here also the query you can see in sql fiddler:

select * from country_subdivisions cs
   where IsoCode IN(
        select cs.IsoCode
          from parent p
         where cs.Level.IsDescendantOf(p.Level) = 1
           and p.CountryISOAlpha2Code = cs.CountryISOAlpha2Code
        --   and (cs.Level.GetLevel() = p.Level.GetLevel() + 1 or cs.Level.GetLevel() = p.Level.GetLevel())
            and cs.Level.GetLevel() = (SELECT MAX(leaf.Level.GetLevel()) FROM country_subdivisions leaf WHERE leaf.Level.IsDescendantOf(cs.Level) = 1 and leaf.HasBoundaries = 1)
           )

As you can see I correctly get the green areas but not the red one.

Any Idea? Do I have been clear?

Thank you

Answer 1

I think the logic is summarized as follows:

Return a parent when:

• That parent has boundaries, and
• Either:
  • it has no children, or
  • it has has at least one child that has no boundaries.

This could be formulated as follows:

select parent.*
from   country_subdivisions parent
where  parent.HasBoundaries = 1
and    0 < (select case 
                   when  count(*) = 0 then 1
                   else  count(case when child.HasBoundaries = 0 then 1 end)
                   end
            from   country_subdivisions child
            where  child.Level.GetAncestor(1) = parent.Level
           );