MySQL - Finding out Balancing Figure From 2 Tables

Question

I have 2 tables like this:

Stock Table
product_id bigint(20)
qty float

Sales Table
product_id bigint(20)
qty float

Sample Data
Stock Table
product_id---qty
1---10
2---11
3---20
4---50
1---10
3---10

Sales Table
product_id---qty
1---2
2---5
3---20
4---40
1---7

I want the following Output after running the Query
product_id---qty
1---11
2---6
3---10
4---10

Well, as spender ask I am trying to more clear the situation.

First of All, let's think that I store
10 quantity of product 1
11 quantity of product 2
20 quantity of product 3
50 quantity of product 4
10 quantity of product 1 (now I have total 20 of product 1)
10 quantity of product 3 (now I have total 30 of product 3)

Secondly, let's think that I sell
2 quantity of product 1
5 quantity of product 2
20 quantity of product 3
40 quantity of product 4
7 quantity of product 1 (now I have sold total 9 of product 1)

Thirdly, I want to know how much stock is now in my hand
11 quantity of product 1 (20-9 = 11)
6 quantity of product 2 (11-5 = 6)
10 quantity of product 3 (30-20 = 10)
10 quantity of product 4 (50-4 = 10)

My Question is: To find out this stock what is the Query?

Thanks in Advance for answering my question.

Answer 1

You question is lacking detail and looks like it might even contain typos in the presented data. I'm going to make the assumption you are trying to calculate the diff between stock quantities and sales quantities, despite your data not actually supporting this (!!!). It looks like you require the following:

select 
    st.product_id,
    sto.qty-st.qty 
from 
    salesTable as st 
    join stockTable as sto on sto.product_id=st.product_id

Answer 2

This answer works in Oracle - don't have MySql so can't test there

select product_id, sum(qty) from
(
  select product_id, qty from stock
  union all
  select product_id, (-1 * qty) from sales
) as a
group by prod

Answer 3

Chris's answer is absolutely correct. But for the information I want to add this one which I found on NET.

SELECT tunion.product_id, (  
(IFNULL((SELECT SUM(s.qty) FROM stock s WHERE s.product_id=tunion.product_id),0))-  
(IFNULL((SELECT SUM(p.qty) FROM sales p WHERE p.product_id=tunion.product_id),0)))  
AS quantity   
FROM (SELECT DISTINCT s.product_id FROM stock s  
UNION ALL SELECT DISTINCT p.product_id FROM  sales p)  
AS tunion GROUP BY tunion.product_id