Hi, Everybody !
I have a small problem.
If I run a query with group by having two outer joins, there, group by is not working i suppose.
Case:
table 1 : Product master having product info
table 2 & 3 : receipt master and transaction having prod_id and qty
table 4 & 5 : despatch master and transaction having prod_id and qty
Requirement:
A Single query which fetches one record from master, multiple records from receipt and despatch because the transaction table is having multiple records for the same product
Query:
select m.prod_id, isnull(sum(R.RECEIVED_QTY),0) receipt, isnull(sum(D.DESPATCH_QTY),0) despatch
from product_master m
left outer join RECEIPT_TR r on m.prod_id=r.prod_id
left outer join DESPATCH_TR d on m.prod_id=r.prod_id
group by r.prod_id, d.prod_id
Problem:
If i have 2 rows in despatch, the ISSUED column doubles the sum of d.ISSUED,
If i have 3 rows, it give thrice the sum and so on.....
Pls Help Me !!