CODEWITHSUNDEEP
mysqlsql
Manos
Manos

Reputation: 11

How to subtract from a column from 2 tables with a same column

So I have 2 tables Named : Stock and Order

table : Stock

+-----------+-----------+
|   Item    |   Number  |
+-----------+-----------+
|   A       |      20   | 
|   B       |       7   |
|   C       |      11   | 
|   D       |      13   |
+-----------+-----------+

table : Order

+-----------+-----------+
|   Item    |   Number  |
+-----------+-----------+
|   A       |       8   | 
|   B       |       2   |
|   C       |       5   | 
|   D       |       9   |
+-----------+-----------+

I need to subtract the column number from stock to order.

The result would be :

table : Stock

+-----------+-----------+
|   Item    |   Number  |
+-----------+-----------+
|   A       |      12   | 
|   B       |       5   |
|   C       |       6   | 
|   D       |       4   |
+-----------+-----------+

I have tried using

SELECT item , number=
(
  (SELECT number FROM stock)-(SELECT number from order))
  FROM order;

Unfortunately it does not work, please help me! And pardon for the formatting.

Upvotes: 0

Views: 268

Answers (2)

Joe Taras
Joe Taras

Reputation: 15379

If two tables are in 1:1 relation -> try this:

SELECT s.item, s.number - o.number
FROM stock s
JOIN order o
    ON s.item = o.item

Otherwise:

SELECT s.item,
s.number - ISNULL(
    (SELECT SUM(o.number)
    FROM order o
    WHERE o.item = s.item), 0)
FROM stock s

Upvotes: 2

apomene
apomene

Reputation: 14389

SELECT B.Item,A.Number - B.Number FROM
Stock A
INNER JOIN Order B 
ON A.Item=B.Item

Upvotes: 2

Related Questions