How to retrive data from four tables

[samikhan83]

i am having a problem in getting data from four tables based on a common field in one query.

1. i have product,open_ba lance,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,pu rchase 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

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;
 

Please help me
Thanx in advance

[code green]

GROUP BY the common field should solve it.
Or any unique field.
T-SQL for example would throw an error for this query because if one field is aggegated they all need to be.
This actually would have helped you in this situation.

[samikhan83]

hi code green,
thanks for your reply.
yes i too thought that but according to my knowledge of database i am not getting to the solution

before i use to do this with getting individual product_id by looping over product table and passing product_id to other tables and getting required data from that tables based on that product id

but i think this is very in-efficient solution and required too much looping and code and i think getting solution in this way is not a good practice.

so, please help me with this

[code green]

Try
GROUP BY p.p_id
This would only return unique p_id.
If that is what you are looking for