Postgresql join three table

Question

I have an inventory database, and i need to calculate the quantity of products after each Buy or sell operation through my stock. So, i have three tables.The equation is like so :

(QtyInital +     (BuyQuantity(IN)  - SELLQUANTITY(OUT)

And Here is the schema of my three tables.

product(pid,name,qteInital,qteStock);
productInBuyFacture(baid,pid,qte,price,subtotal);
productInSellFacture(bsid,pid,qte,price,subtotal);

i want to calculate the current quantity of the stock via a trigger. I tried to do this via an SUB QUERYIES like so,

select ((select qteInital from product where id = 3) + 
(select qte from productInBuyFacture where pid = 3 ) - 
(select qte from productInSellFacture where pid = 3) as currentQuantity ;

And what happened when you tried that?

Gordon Linoff
– Gordon Linoff

2015-01-11 16:15:09 +00:00
Commented Jan 11, 2015 at 16:15 — Gordon Linoff
– Gordon Linoff, Commented Jan 11, 2015 at 16:15

Gordon Linoff · Accepted Answer · 2015-01-11 16:16:40Z

1

My guess is that you need summation and to fix the parentheses so they balance:

select ((select coalesce(sum(qteInital), 0) from product where id = 3) + 
        (select coalesce(sum(qte), 0) from productInBuyFacture where pid = 3 ) - 
        (select coalesce(sum(qte), 0) from productInSellFacture where pid = 3)
       ) as currentQuantity ;

The coalesce() is to prevent a problem with non-matches. A NULL in an arithmetic expression will generally cause the entire expression to return NULL.

answered Jan 11, 2015 at 16:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user_0 · Accepted Answer · 2015-01-11 17:28:05Z

1

You can also try with explicit join:

SELECT pro.pid, pro.QtyInital, COALESCE(SUM(buy.qte), 0) AS buyqty, COALESCE(SUM(sell.qte), 0) AS sellqty
FROM product AS pro
LEFT JOIN productInBuyFacture AS buy
ON pro.pid = buy.pid
LEFT JOIN productInSellFacture AS sell
ON pro.pid = sell.pid
GROUP BY pro.pid, pro.QtyInital

answered Jan 11, 2015 at 17:28

user_0

3,38323 silver badges38 bronze badges

Collectives™ on Stack Overflow

Postgresql join three table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related