To subtract two columns which are in two different tables

[manikishore]

I have 2 tables, items and sold . In items table I have itemid , item name , quantity and in sold table I have itemid , peopleid, itemname , soldquantity. I want the output as itemid, itemname,remquantity(quantity-soldquantity) and where will the final output will store?

[Rabbit]

Join the two tables on itemid and do the subtraction there.

You shouldn't store the final output. It is better to create it as a view so the data is always up to date.

[Luuk]

Is your only question where to store the output?
Because that answer depends on your needs, and Rabit gave a good answer already.....

But if your question is also about how to retrieve the data from MySQL than, please give the SQL statement you tried but 'did not work for this purpose'

[manikishore]

can u please provide sql query to my question?

[Rabbit]

Please make an attempt at the code using the algorithm provided. If you run into any problems, post the query you tried and any error messages you get.

[manikishore]

select items.iid, SUM(items.quantity)-sum(soldtable.soldquantity) as remquantity from items inner join soldable on items.iid = soldtable.iid group by iid,pid;
My tables are:
items:
iid    iname    quantity
1    Rice    40
2    Sugar    20
3    Dall    30
soldtable:
iid    Pid    Soldquantity
1    P1    5
1    P2    6
2    P1    4
2    P2    5
I'm getting the output as:
iid    Remquantity
1    35
1    34
2    16
2    15
But I want the output as 
iid    remquantity
1    29
2    11

please give me query for this one.

[Rabbit]

1) Don't group by pid
2) Use the max of the items quantity, not the sum

[manikishore]

Ok sir its working. Thank you for your help.