Search code examples
sqlsql-servercommon-table-expressionhierarchical-datarecursive-query

How to find the parent and child relation in sql

I have the data like below and trying to get the sum of time taken by parent.

Input

ID_P    ID_C    SLA FL
1       2     0.2   Y
2       3     0.5   N
3       4     0.5   N
8       9     1.5   Y
9       10    0.1   N
10            0.2   N

Expected output

ID_P    Sum(SLA)
1       1.2
8       1.8

Can someone please help me with the SQL.

Solution

  • You can use a recursive query. The idea is to start from the parent rows - which, as I understand your data, are identified with column fl. Then you can follow the links to the children. The final step is aggregation:

    with cte as (
    select idp_p, id_c, sla from mytable where fl = 'Y'
    union all
    select c.id_p, t.id_c, t.sla
    from cte c
    inner join mytable t on t.id_p = c.id_c
)
select id_p, sum(sla) as sum_sla from cte group by id_p