Dear all,
I am having the following problem and did not know the solution.

I have three related tables.

product
purchase_order
git_order.

In product table

product_id product_name
1 barrings
2 wheel rim
3 rodd

purchase_order

po_id product_id package_id numbers received etc.
1 1 2 100 50
2 2 4 250 60
3 1 1 200 40
4 1 3 200 40
5 2 5 200 40
6 1 1 200 40
7 1 5 200 40


git_order
git_id product_id package_id numbers received etc.
1 1 2 100 50
2 2 4 250 60
3 1 1 200 40
4 1 3 200 40
5 2 5 200 40
6 2 1 200 40
7 1 5 200 40


The output i want is.

product_id sum(numbers)in 2ndtable | sum(numbers)in 3rdtable
1 900 700
2 450 650

i need a query to do this. i m new to this. i spent lot of time in this but couldnt resolve. please help me.



This is my present query:

Code:
SELECT     p.product_id,     po.ponumbers,     g.gonumbers FROM Product p LEFT JOIN (SELECT Product_id, SUM(numbers) AS ponumbers         FROM purchase_order         GROUP BY product_id ) AS po ON p.product_id = po.product_id LEFT JOIN (SELECT product_id, SUM(numbers) AS gonumbers         FROM git_order         GROUP BY product_id) AS g ON p.product_id = g.product_id
this gives the following output:



Array ( [0] => 1 [product_id] => 1 [1] => 19103 [ponumbers] => 19103 [2] => 18410 [gonumbers] => 18410 )



but i want not only for 1 product, i need the query to do the same for all the products that i have in my product table.

any help??

The result should look something like this.

Product ID PO Count GIT Count
1 19103 18410
2 xxxxx xxxxx
3 xxxxx xxxxx