with a as (
select 1 id,2 p_id from dual
union
select 2 id,3 p_id from dual
union
select 2 id,4 p_id from dual
union
select 3,5 from dual
union
select 7,6 from dual
)
select l.id,decode(r.id,null,0,1) cnt from a l ,a r
where l.id=r.p_id(+)
with a as (
select 1 id,2 p_id from dual
union
select 2 id,3 p_id from dual
union
select 2 id,4 p_id from dual
union
select 3,5 from dual
union
select 7,6 from dual
)
select l.id,decode(r.id,null,0,1) cnt from a l ,a r
where l.id=r.p_id(+)