HierarchyId quick fun

--drop table dbo.Earth
create table dbo.Earth
  (
    Id hierarchyid not null,
    Path as Id.ToString(),
    Data varchar(500),
    primary key clustered (id))

insert dbo.Earth (id,data)
values
  ('/1/1/1/1/','Monkey'),
  ('/1/1/1/2/','Horsey'),
  ('/1/1/1/3/','Piglet'),
  ('/1/1/1/','Mammal'),
  ('/1/1/','Animal'),
  ('/1/1/2/','Insect'),
  ('/1/1/2/14/','Ant'),
  ('/1/2/','Plant'),
  ('/1/2/1/','Tree'),
  ('/1/2/1/1/','Birch'),
  ('/1/2/1/2/','Oak'),
  ('/1/','Living stuff'),
  ('/2/','Moving stuff'),
  ('/2/1/','Cars'),
  ('/2/2/','Hurricanes')

--get all living stuff categories

select *
from dbo.Earth
where id.GetLevel()=3
  and id.IsDescendantOf('/1/')=1
  
--get all animals

declare @animals hierarchyid='/1/1/1/'select *
from dbo.Earth
where id.IsDescendantOf(@animals)=1
and id != @animals

--Get animal granny

select *
from dbo.Earth
where id=@animals.GetAncestor(2)