MySQL Query for subtracting values from 2 tables-CodePudding

Hello I have this tables

Table A
ID  Name   Price QTY
1   name1  1000  10
2   name2  1200  5

Table B
ID  Name   Price QTY
1   name1  1000  2

I want to achieve

Table C
ID  Name   Price QTY
1   name1  1000  8
2   name2  1200  5

With my query SELECT DISTINCT ta.name, ta.price,(ta.quantity - tb.quantity) AS quantity, FROM TableA AS ta INNER JOIN TableB AS tb ON ta.id = tb.id

What I get is this

Table C
ID  Name   Price QTY
1   name1  1000  8
2   name2  1200  3

I sorry I really cant think of a way how to achieve what I want. Thanks for your help.

CodePudding user response：

Your query actually gets you one line instead of two:

-- result set
# name, price, quantity
name1, 1000, 8

Based on your expected output,we can use:

SELECT  ta.ID, ta.name, ta.price, ifnull((ta.QTY - tb.QTY),ta.QTY) AS quantity 
FROM TableA AS ta 
LEFT JOIN TableB AS tb 
ON ta.name = tb.name and ta.price=tb.price;
-- result set:
# ID, name, price, quantity
1, name1, 1000, 8
2, name2, 1200, 5

This is the last updated answer I can give you for you updated request.Please consider firing a new question if it does not fulfill.

-- Supposing your latest tables have the following data.
insert TableA values
(1 ,  'name1' , 1000 , 10)
,(2 ,  'name2' , 1200 , 5);
insert TableB values
(1 ,  'name1' , 1000 , 2),
(2 , 'name2', 1000 , 3)
;

-- You probably want this:
SELECT ta.ID, ta.name, ta.price, ifnull((ta.QTY - tb.QTY),ta.QTY) AS quantity 
FROM TableA AS ta 
LEFT JOIN TableB AS tb 
ON ta.name = tb.name and ta.price=tb.price
union
SELECT  Id , name , price , QTY 
from TableB t
where not exists (select * from TableA where t.name=name and t.price=price)
;

-- result set:
# ID, name, price, quantity
1, name1, 1000, 8
2, name2, 1200, 5
2, name2, 1000, 3