CODEWITHSUNDEEP
sqldb2
Arrival
Arrival

Reputation: 11

How to get the leaf nodes by recursive query in db2?

In DB2 SQL I have a table which contains 3 columns: parent_id, id, and count. It is a hierarchy table. I need a recursive query to get all leaf nodes and their total count, and another query to get all leaf nodes and their total count plus the cost of each node (the price in stored in another table (id, price))

I tried this query but it did not work:

 with COMP_P (COMPONENT,SUBCOMPONENT,SUBCOMPCOUNT) as (
(select C.COMPONENT,C.SUBCOMPONENT,C.SUBCOMPCOUNT from COMPS C WHERE C.COMPONENT = 'A')
UNION ALL 
(SELECT P.COMPONENT,C.SUBCOMPONENT,C.SUBCOMPCOUNT * P.SUBCOMPCOUNT from COMPS C,COMP_P P WHERE P.SUBCOMPONENT = C.COMPONENT ))
SELECT COMPONENT,SUBCOMPONENT,sum(SUBCOMPCOUNT) As Count FROM COMP_P group by COMPONENT,SUBCOMPONENT ;

It gives me leaf nodes and other. I tried this for the second part:

with COMP_P (COMPONENT,SUBCOMPONENT,SUBCOMPCOUNT) as (
(select C.COMPONENT,C.SUBCOMPONENT,C.SUBCOMPCOUNT from COMPS C WHERE C.COMPONENT = 'A')
UNION ALL 
(SELECT P.COMPONENT,C.SUBCOMPONENT,C.SUBCOMPCOUNT * P.SUBCOMPCOUNT from COMPS C,COMP_P P WHERE P.SUBCOMPONENT = C.COMPONENT ))
SELECT COMPONENT,SUBCOMPONENT,sum(SUBCOMPCOUNT) As Count,sum(SUBCOMPCOUNT) * partcosts.cost FROM COMP_P,partcosts where partcosts.partid.id=COMP_P.SUBCOMPONENT group by COMPONENT,SUBCOMPONENT ;

Upvotes: 1

Views: 1541

Answers (1)

Xavier Holt
Xavier Holt

Reputation: 14619

If you're just trying to get leaf nodes, you shouldn't need to do a recursive query - you can just check for the existence of children:

SELECT * FROM table AS t1 WHERE NOT EXISTS (SELECT * FROM table AS t2 WHERE t2.parent_id = t1.id)

That'll get you all the leaf nodes, and then it's just a matter of adding in counts and a join to get the information you need.

Hope that helps!

Upvotes: 1

Related Questions