By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,035 Members | 969 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,035 IT Pros & Developers. It's quick & easy.

Group By with 2 outer joins

P: 1
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 !!
Feb 7 '08 #1
Share this Question
Share on Google+
3 Replies


P: 65
Hi,

Try this query. It may work as per u r requirement . .

Expand|Select|Wrap|Line Numbers
  1. select m.prod_id, isnull(sum(R.RECEIVED_QTY),0) receipt, isnull(sum(D.DESPATCH_QTY),0) despatch
  2. from product_master m
  3. left outer join RECEIPT_TR r on m.prod_id=r.prod_id
  4. left outer join DESPATCH_TR d on m.prod_id=d.prod_id
  5. group by m.prod_id

Regards,
Nedu. M
Feb 7 '08 #2

P: 1
Hello all

I too face similar problem but unable to find a solution .
Suppose in my query i am trying to fetch more than one fields then it forces me to includes all those fields in the group by clause too.

How come it seems to be working fine?

Can you provide a solution

Thanks
bonnie

Hi,

Try this query. It may work as per u r requirement . .

Expand|Select|Wrap|Line Numbers
  1. select m.prod_id, isnull(sum(R.RECEIVED_QTY),0) receipt, isnull(sum(D.DESPATCH_QTY),0) despatch
  2. from product_master m
  3. left outer join RECEIPT_TR r on m.prod_id=r.prod_id
  4. left outer join DESPATCH_TR d on m.prod_id=d.prod_id
  5. group by m.prod_id

Regards,
Nedu. M
Mar 27 '08 #3

Delerna
Expert 100+
P: 1,134
Hello all

I too face similar problem but unable to find a solution .
Suppose in my query i am trying to fetch more than one fields then it forces me to includes all those fields in the group by clause too.

How come it seems to be working fine?

Can you provide a solution

Thanks
bonnie
Hi bonnie
There is no solution. These queries are called agregate queries and the simple fact is that every field selected in an aggregate query must be aggregated.
It dosn't make sense to aggregate only some fields and not others.
GROUP BY is an aggregate function so any field in your selection that you don't want to be SUM() or MIN() or MAX() or AVG() or any of the other aggregate functions must be put into the GROUP BY clause.
Thats just the way it is.
Mar 28 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.