sql server - Find all nodes that have children with hierarchyid -

given table:

create table employee (     empid int primary key identity,     empname varchar(100) not null,     position hierarchyid not null )  insert employee (empname, position) values ('ceo', '/'),     ('coo', '/1/'),     ('cio', '/2/'),     ('cfo', '/3/'),     ('vp financing', '/3/1/'),     ('accounts receivable', '/3/1/1/'),     ('accountant 1', '/3/1/1/1/'),     ('accountant 2', '/3/1/1/2/'),     ('accountant 3', '/3/1/1/3/'),     ('accounts payable', '/3/1/2/'),     ('accountant 4', '/3/1/2/1/'),     ('accountant 5', '/3/1/2/2/'),     ('dba', '/2/1/'),     ('vp of operations', '/1/1/') 

how find rows don't have child nodes?

i have following seems work, seems there should less convoluted way:

select * (     select          *,           case              when (select top 1 e.position dbo.employee e position.getancestor(1) = employee.position) null                  cast (0 bit)             else                  cast (1 bit)         end hasdescendants                   dbo.employee ) managers hasdescendants = 0 

see other stack overflow question: find leaf node records using hierarchyid

select a.* employee  left outer join employee b on a.position = b.position.getancestor(1) b.position null;