Linesofcode Linesofcode - 5 months ago 12
MySQL Question

MySQL subtract if found, show null or zero if not

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):


  1. The item 1 sold 10 units in the purchase document 103

  2. The item 1 sold 5 units in the purchase document 104

  3. 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.

Answer
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