select id,
case when p_id is null then 'Root '
when id in (select distinct p_id from tree where p_id is not null) then 'Inner'
else 'leaf' end Type
from tree
order id
