Recursion with postgresql query

Question

I am trying to fetch end child data of grandparent data.

I have below 2 tables, one is master table having parent and child data, other is the relation table for that master table data.

based on these data, I want parent data with their end child data.

for that I have tried below recursive query but not geting any related data.

At last I want to use those data in below way with java batch.

if I pass child_data 331 and 327 one by one then it will provide below results respectively.

@set ko_id = '331'

select parent_id,child_id,count(parent_id) from (
 WITH RECURSIVE ancestors (parent_id) AS (
  SELECT distinct t.parent_id ,t.parent_id as extra_id,t.child_id , msok.data_type -- and find all its ancestors
  FROM public.data_relation AS t 
    JOIN data_relation AS a ON t.child_id = a.parent_id or t.child_id = a.child_id 
    left join data_master msok on msok.id = t.parent_id
  where a.child_id = :ko_id
), 
descendants (parent_id) AS (
  SELECT  parent_id ,extra_id as extra_id,child_id, data_type FROM ancestors  
  UNION ALL
  SELECT t.child_id,d.parent_id as extra_id,t.child_id, msok.data_type -- and find all their descendants
  FROM public.data_relation AS t 
    JOIN descendants AS d ON t.parent_id = d.parent_id
    left join data_master msok on msok.id = t.child_id 
) 
SELECT 
  parent_id, extra_id, child_id, data_type
FROM 
  descendants where data_type ='1') abc group by parent_id,child_id

Answer 1

This query should help. The first CTE ("with" part) provides all relations there are in the table. The second CTE filters only end child records out.

And the final part - the main query - searches for parents having 331 as a child and shows you those.

with recursive result_q as (
  select 1 debug_step,  parent_data, child_data 
    from data_rel
  union all
  select debug_step + 1, q.parent_data, r.child_data
    from data_rel r
    join result_q q
      on q.child_data = r.parent_data 
),
parent_end_childs as (
  select q.parent_data, q.child_data 
    from result_q q
   where not exists (select 1 from data_rel r where r.parent_data = q.child_data)
   order by parent_data, child_data)
   
select pec.*
  from parent_end_childs pec
  join parent_end_childs pec_1
    on pec.parent_data = pec_1.parent_data
 where pec_1.child_data = 331

dbfiddle