CODEWITHSUNDEEP
mysqljoin
cybrz
cybrz

Reputation: 33

Mysql subtract from another table

I have tried to search this forum for an answer but can't seem to find one.

My problem: I have 3 tables. One with items, one with in-going deliveries and one with outgoing deliveries.

Table called items:

id | item_name 
----------------
1  | Bike
2  | Helmet
3  | Pedal
4  | Light

Table called ingoing:

id | item_id | quantity
-----------------------
1  | 2       | 5
2  | 3       | 2
3  | 4       | 1
4  | 1       | 5
5  | 2       | 4
6  | 1       | 6
7  | 3       | 5

Table called outgoing:

id | item_id | quantity
-----------------------
1  | 3       | 2
2  | 1       | 1
3  | 2       | 3
4  | 3       | 4
5  | 1       | 2
6  | 2       | 1
7  | 4       | 1

What I want to do is get the total amount in stock by subtracting the outgoing deliveries from the in-going and order it by the item with the least amount.

Maybe there is a better way to do this?

This is the query that I got, but the SUM amounts isn't correct. Can someone help me and explain why the SUM amounts isn't correct and how should I solve this with the best way?

SELECT items.id AS ID, 
       items.item_name, 
       Sum(ingoing.quantity) - Sum(outgoing.quantity) AS InStock
FROM   items
       LEFT JOIN ingoing 
              ON ingoing.item_id = items.id
       LEFT JOIN outgoing 
              ON outgoing.item_id = items.id
 GROUP BY ID
 ORDER BY InStock ASC

This is the result I want from this:

ID | item_name | InStock
---------------------------
4  | Light     | 0
3  | Pedal     | 1
2  | Helmet    | 5
1  | Bike      | 8

What I Get:

ID | item_name | InStock
---------------------------
4  | Light     | 0
3  | Pedal     | 2
2  | Helmet    | 10
1  | Bike      | 16

Upvotes: 2

Views: 838

Answers (2)

Raghav Patel
Raghav Patel

Reputation: 843

Try this query:

SELECT items.id AS ID, items.item_name, (SELECT SUM(quantity) from ingoing 
WHERE ingoing.item_id = items.id) - (SELECT SUM(quantity) from outgoing WHERE
outgoing.item_id = items.id) AS InStock FROM  items ORDER BY InStock ASC;

Upvotes: 0

eggyal
eggyal

Reputation: 125835

It can be easy to forget the multiplicative effect of a join. When you encounter problems like this, check the result of the join, prior to filtering/grouping:

SELECT items.id, 
       items.item_name,
       ingoing.id  AS ingoing,
       outgoing.id AS outgoing
FROM   items
       LEFT JOIN ingoing 
              ON ingoing.item_id = items.id
       LEFT JOIN outgoing 
              ON outgoing.item_id = items.id

See it on sqlfiddle.

As you can see, the resultset contains multiple records with the same ingoing.id values, and multiple records with the same outgoing.id values. This is because each ingoing record for a particular item has joined with every outgoing record for that same item: thus there are 4 pedals in the resultset (2 incoming x 2 outgoing), etc.

Aggregating each table by item prior to joining (and thus ensuring that there is only 1 record per item on each side of the join) will achieve what you're after:

SELECT items.id AS ID, 
       items.item_name, 
       ingoing.quantity - outgoing.quantity AS InStock
FROM   items
       LEFT JOIN (
         SELECT   item_id AS id, SUM(quantity) AS quantity
         FROM     ingoing
         GROUP BY item_id
       ) AS ingoing USING (id)
       LEFT JOIN (
         SELECT   item_id AS id, SUM(quantity) AS quantity
         FROM     outgoing
         GROUP BY item_id
       ) AS outgoing USING (id)
ORDER BY InStock ASC

See it on sqlfiddle.

Note that your problem would have been greatly simplified by only having a single underlying table of stock movements, with positive quantities indicating movements in one direction and negative quantities indicating movements in the opposite direction: then a simple groupwise summation of the whole table would yield your desired results.

Upvotes: 3

Related Questions