Sum Price Of Childs In Other Table Mysql

I have two table one store data child and parent hierarchy and other paths and descendant +----------+------------+-----------+ | userid | parent | price | +----------+--

Solution 1:

Try this;)

select ancestor_id as userid, sum(b.price) as total
from webineh_prefix_nodes_paths_tmp a 
join webineh_prefix_nodes_tmp b 
on b.userid = a.descendant_id
where a.ancestor_id in (select userid from webineh_prefix_nodes_tmp where parent = 1)
groupby ancestor_id

SQLFiddle Demo

Edited

select ancestor_id as userid, sum(b.price) as total
from webineh_prefix_nodes_paths_tmp a 
join webineh_prefix_nodes_tmp b 
on b.userid = a.descendant_id
inner join webineh_prefix_nodes_tmp c
on a.ancestor_id = c.userid
and c.parent = 1groupby ancestor_id

SQLFiddle Demo

Solution 2:

try this

select sum(b.price) from webineh_prefix_nodes_paths_tmp a join webineh_prefix_nodes_tmp b on (b.userid = a.descendant_id) where a.ancestor_id in ( 1,2,3) GROUPby ancestor_id