1. i have product,open_balance,purchase,sales tables and product_id is primary key of product and foreign key in rest of the tables
2. i want to get the sum of the open_balance,purchase and sales for a product based on product key to get current quantity in stock
i used Left Join i think i am not writing the query correctly because it is doubling the value by repeating data based on conditions in query
This is the query
Expand|Select|Wrap|Line Numbers
- select
- p.p_manufacturer,
- p.p_name,
- sum(oqp.op_q_p_quant)as open_balance,
- sum(pqp.pur_q_p_quant) as purchase_balance,
- sum(sqp.sale_q_p_quant) as sales_balance
- from product p
- Left Join purchase_quant_price pqp
- on p.p_id=pqp.pur_q_p_pro_id
- Left Join open_quant_price oqp
- on p.p_id=oqp.op_q_p_pro_id
- left join sales_quant_price sqp
- on p.p_id=sqp.sale_q_p_pro_id;
Thanx in advance