How to subtract the old value with current value from database?-CodePudding

my question is I have 2 tables. let say Table Old and Table Current. Both tables have columns Quantity. Old.Quantity is the old values, and Current.Quantity is the current values.

Table Old :

id  pro.no  Quantity
1    123        3

Table Current :

id  pro.no  Quantity
1    123       2

SQL :

SELECT A.`Quantity`, B.`Quantity`
FROM Table Current A 
LEFT JOIN Table Old B ON B.`pro.no` = A.`pro.no`
WHERE A.`id` = '1'

So, I want to subtract both values to get the new values which is 1 So that, when user key in the pro.no into the textbox, it'll show them they new value for Quantity is 1.

I am using VB.NET for the backend, and I'm still new for this language too.

CodePudding user response：

You can simply subtract both columns, however as this is a left join you'll need to care for the null values:

SELECT CASE
      WHEN o.quantity IS NULL THEN c.quantity
      ELSE c.quantity - o.quantity
    END
  FROM current c LEFT JOIN old o ON ... WHERE ...

or alternatively

SELECT c.quantity - CASE WHEN o.quantity IS NULL THEN 0 ELSE o.quantity END
  FROM current c LEFT JOIN old o ON ... WHERE ...