Find all direct and indirect end child

I have table like this :

ID Child ID Flag
A01 C01 Y
A01 C02 Y
A01 AC01 N
AC01 C03 Y
AC01 AC02 N
AC02 C04 Y

So here A01 is direct parent of C01,C02 and AC01. AC01 is direct parent of C03 and AC02 and AC02 is direct parent of C04.C01,C02,C03 and C04 don't have any child. So they are like end children. that's why their flag is 'Y'. But AC01 and AC02 are not like pure end child.And also C03 and C04 are indirect children of A01.

I want to list down all direct and indirect end children(Flag ='Y') of a particular ID.Something like below:

ID Child ID
A01 C01
A01 C02
A01 C03
A01 C04

The database I am using Oracle 11G

I have tried recursive WITH clause.

WITH  child (id,child_id) AS (
    SELECT  id,
            child_id,
            0 AS level
    FROM my_table
 
    UNION ALL
 
    SELECT  e.id, 
            e.child_id,
            level + 1
    FROM my_table e 
JOIN child s 
ON e.id = s.child_id
)
 
SELECT 
    m.id,
    s.child_id
FROM child s 
JOIN my_table m 
ON s.id = m.child_id;

But I am not getting desired output.

Solution

You don't need recursive with clause for this problem. It can be done, but recognizing "end" children is not trivial.

Instead, an old-fashioned connect by query (hierarchical query) can do quick work of this. Note that in my query (and output) I didn't include the ID column - you can include it if you like, but it gives you absolutely no information; it simply copies the input ('A01') down the column. That is something you would know before running the query - actually, before even writing the query!

The key is the where clause; in a hierarchy, "end" children are known as leaves, and connect_by_isleaf returns 1 for leaves, 0 otherwise.

select  child_id
from    my_table
where   connect_by_isleaf = 1
start   with id = 'A01'
connect by id = prior child_id
;

If you insist on a solution using the recursive with clause, here is one way. Note the search clause at the end - it is key to the correct definition of the is_leaf column in the next subquery. One advantage of this approach is that it mimics Oracle proprietary hierarchical queries (connect by) using SQL standard features (recursive with clause).

with
  r (child_id, lvl) as (
    select  child_id, 1
      from  my_table
      where id = 'A01'
    union all
    select  t.child_id, r.lvl + 1
      from  my_table t join r on t.id = r.child_id
  )
  search depth first by child_id set ord
, prep (child_id, is_leaf) as (
    select child_id,
           case when lead(lvl) over (order by ord) > lvl then 0 else 1 end
    from   r
  )
select child_id
from   prep
where  is_leaf = 1
;