0

I have a problem with regards to the stated title. I want to get the stocks_on_hand from these two tables, namely:

stocks_added
product_id  quantity_added
ANK001      50

stocks_released
product_id  quantity_released
ANK001      20

after getting the stocks_on_hand (result of the two tables), i want to join it to the products table:

product_id  product_name  price
ANK0001     ANKLET        200
BenMorel
  • 34,448
  • 50
  • 182
  • 322
jaegyo23
  • 23
  • 1
  • 12

1 Answers1

0

Use joins and sum():

select
    p.product_id, product_name, price,
    coalesce(sum(quantity_added), 0) - coalesce(sum(quantity_released), 0) as stocks_on_hand 
from products p
left join stocks_added a on a.product_id = p.product_id
left join stocks_released r on r.product_id = p.product_id
group by p.product_id, product_name, price

Using outer (ie left) joins means you'll get a row for every product whether or not there are rows in the stock movement tables. Using coalesce() means a default value of zero is used when there are no rows in the stock table.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Hi @Bohemian. I have tried the code you suggested and it worked :) but what if, lets say, i have a duplicate ANK001 in the stocks_added table but with a different quantity like 50, how will i add the GROUP BY syntax using the code you gave? – jaegyo23 Mar 19 '14 at 16:30
  • @jaegyo23 The query will work as is. The query returns the *total* of all `quantity_added` for the product (no matter how many rows there are) minus the *total* of all `quantity_released` for the product (no matter how many rows there are). – Bohemian Mar 19 '14 at 22:34