Kakul Sarma Kakul Sarma - 4 months ago 8
MySQL Question

SUM OF Parent Raise SQL

HI I have two table like this

TBL 1

Productid Childid
1 null
2 1
3 1
4 1
5 null
6 5
7 6
8 null

Tbl 2
Productid amount
1 300
2 100
2 100
2 300
5 100


As you can see product 1 is parent and it has 2,3,4 child and it Raise 500 from child and 300 from Parents . That means 1 Raise 800 in total . now i want This type of Result

SQL :
SELECT t1.productid as pid, SUM(t3.amount) as amount
FROM TBL1 t1
JOIN TBL1 t2
ON(t1.productid = t2.childid)
JOIN TBL2 t3
ON(t3.productid IN(t2.childID,t2.productID))
GROUP BY t1.productid

RESULT
pid amount

NULL 900


But i want Like This

RESULT
pid amount
1 800
5 100

Answer

Try this:

select t1.productid as pid, sum(t3.amount) as amount
from tbl1 t1
inner join (
    select productid, coalesce(childid, productid) as childid from tbl1
) t2
on t1.productid = t2.childid
inner join tbl2 t3
on t2.productid = t3.productid
group by t1.productid

SQLFiddle Demo