CODEWITHSUNDEEP
mysqlsql
user197324
user197324

Reputation: 47

Subtracting value of a particular column where a value of particular column is same in both tables

My db has two tables stock and damage.

stock table looks like

item_code, ss_no, item_name, rack_no, shelf_no, cold_storage, batch_no, qty, packing, expiry_date, mrp, purchase_price, selling_price, margin, formulation, stock_date, min_qty, ss_flag_id, ban_flag_id, sales_discount
'1', 1, 'ABZORB POWDER', 'A-1', ' ', ' ', '9086626', 18, 1, '2017-06-01', 87.00, 66.29, 87.00, 0.00, 'POWDER', '2015-05-11', 0, 0, 0, 0.0

damage table looks like the below

damage_stock_date, invoice_no, invoice_date, dist_name, contact_no, item_code, item_name, batch_no, mfr_name, expiry_date, qty, damaged_qty, unit_price, unit_vat, unit_discount, sub_total, total_amount, remarks, ds_flag_id, packing
'2015-06-19', '56', '2015-06-19', 'Ganapati Drugs', '', '0', 'SAXIM_', '1', '', '', 50, 10, 2.00, 5.00, 0.00, 21.00, 21.00, '', 0, 0

If I want to select a row from stock where the value of item_name column in both the tables, I would use

select * from stock s where item_name in
( select item_name from damage);

which does the job exactly what I want.

Now I want to subtract the value of qty col in damage from stock's qty col where the value of item_name from both the columns must be same.

I think I have to use variables but I don't know how..

Upvotes: 1

Views: 49

Answers (2)

Raging Bull
Raging Bull

Reputation: 18737

Try using join:

select *, (s.qty-d.qty) as available
from stock s LEFT JOIN
     damage d on d.item_name=s.item_name
where d.qty IS NOT NULL

Example in SQL Fiddle.

Explanation:

A left join with table damage with condition d.qty IS NOT NULL would do the same thing. It is same as using IN (as in your question). But the advantage is that, you can use the columns of damage table in the main query.

So you can find the available quantity by (s.qty-d.qty)

EDIT:

For updating the table:

UPDATE  stock s LEFT JOIN 
        damage d ON d.item_name = s.item_name
SET     s.qty=(s.qty-d.qty)
WHERE   d.qty IS NOT NULL

Upvotes: 0

200_success
200_success

Reputation: 7582

It looks like there can be multiple damage entries for each stock item. Therefore, you need to use an aggregating subquery on damage before joining.

SELECT *, stock.qty - total_damage.qty AS remaining_qty
    FROM stock,
        (SELECT item_name, SUM(qty) AS qty
            FROM damage
            GROUP BY item_name) AS total_damage
    WHERE stock.item_name = total_damage.item_name;

Upvotes: 1

Related Questions