vendredi 17 juin 2016

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.

Aucun commentaire:

Enregistrer un commentaire