MySQL subtract if found, show null or zero if not

Question

I need to match values from the same table and show the subtraction of them. However, some values might not match (because they do not exist) and in that case I should show null or zero.

Table purchase_items
---------------------
ID | purchase_id | item_id | quantity
1    103           1         10
2    103           2         5 
3    103           3         20
4    104           1         5
5    104           2         5

The desired output is:

item_id | Original quantity | New quantity | Total
1         10                  5              5
2         5                   5              0
3         20                  null/0         20

Currently I'm not able to present the last row. With the query below I can only reproduce:

item_id | Original quantity | New quantity | Total
1         10                  5              5
2         5                   5              0

SELECT 
    original.quantity AS `Original quantity`,
    new.quantity AS `New quantity`,
    (original.quantity - new.quantity) AS total
FROM
    purchase_items AS original,
    purchase_items AS new
WHERE
    original.purchase_id = 103 AND new.purchase_id = 104 AND original.item_id = new.item_id

And I do realize the problem is within original.item_id = new.item_id but how can I overcome this problem?

EDIT: It seems my post is confusing. I'll try to explain it in a better way.

I'm trying to understand the items sold in the previous document and compare to the items sold in the current document.

My table is the following:

Table purchase_items
---------------------
ID | purchase_id | item_id | quantity
1    103           1         10
2    103           2         5 
3    103           3         20
4    104           1         5
5    104           2         5

From this values you can understand the following situations from item 1 (and same applies to item 2):

The item 1 sold 10 units in the purchase document 103
The item 1 sold 5 units in the purchase document 104
The item 1 has a total of 5 units remaining (10-5 = 5)

So, the output must be:

item_id | Original quantity | New quantity
1         10                  5          
2         5                   5

Now, if you look at the table there's an item 3, which was sold in the purchase document 103 but not in the purchase document 104! With that said, the output should be

item_id | Original quantity | New quantity
1         10                  5          
2         5                   5
3         20                  0/null

Forget about the subtraction, because I can do it in PHP if necessary.

Take a look at this SQLFiddle.

@Jens ...that isn't much help, like the post you deleted wasn't. — Linesofcode, Jun 17 '16 at 10:10
With your sample data and query, seems to not get the result you've post. — Blank, Jun 17 '16 at 10:27
The only thing wrong in my code was the numbers of `purchase_id` and I've correct them now. — Linesofcode, Jun 17 '16 at 10:35
I think you want to calculate a running sum? Look e.g. at [Running Sums for Multiple Categories in MySQL](http://stackoverflow.com/questions/5032360/running-sums-for-multiple-categories-in-mysql). Since your first row is special (all others have an implied "-"-sign (you might want to think about if you want to add it for real!), you can use `if(@item = item_id,@sum-Quantity,Quantity)` in the second approach of the first answer. In your query, the very hard thing is that you don't have an order. Try adding some more rows for the same item_id and see for yourself! — Solarflare, Jun 17 '16 at 10:42
In that case, you can use P.Salmons solution with your values in the `on`-clause: `on p1.purchase_id = 103 and p2.purchase_id = 104 and p1.item_id = p2.item_id`. Doesn't that work for you? — Solarflare, Jun 17 '16 at 11:21
Yeah, never mind. I gave another try on his code with some changes and worked perfectly. Thanks. — Linesofcode, Jun 17 '16 at 11:27

score 0 · Accepted Answer · answered Jun 17 '16 at 10:44

USE sandbox;
/*
create Table purchase_items
(ID int, purchase_id int, item_id int, quantity int);
insert into purchase_items
values
(1,    1,             1,         10),
(2,    1,             2,         5 ),
(3,    1,             3,         20),
(4,    2,             1,         5),
(5,    2,             2,         5)
;
item_id | Original quantity | New quantity | Total
1         10                  5              5
2         5                   5              0
3         20                  null/0         20 

*/

    select  p1.id,p1.quantity as old_quantity,p2.quantity as new_quantity,
            case 
            when    p2.quantity is null then p1.quantity
            else    p1.quantity - p2.quantity
            end     as total
    from    purchase_items p1
    left outer join purchase_items p2 on p2.purchase_id = p1.purchase_id + 1 and p2.item_id = p1.item_id
    where   p1.purchase_id = 1

score 0 · Answer 2 · answered Jun 17 '16 at 11:39

Using left join helps, as i wrote in y comment. This query gives yu the correct result:

SELECT original.item_id,
    original.quantity AS `Original quantity`,
    new.quantity AS `New quantity`,
    (original.quantity - new.quantity) AS total
FROM
    purchase_items AS original left join
    purchase_items AS new on original.item_id = new.item_id
 and
    original.purchase_id = 103 AND new.purchase_id = 104 
    group by original.item_id

As you can see in the SQL-Fiddle

MySQL subtract if found, show null or zero if not

2 Answers2