Multiple Table through Join

Question

I have Three Tables 1. ItemMaster 2. GRN 3. Issue

Item Master
------------------
ItemCode          ItemDescr
--------          ---------
1                 Test1
2                 Test2
3                 Test3
4                 Test4
5                 Test5

GRN Table
-------------------
ItemCode          grnQty
--------         --------
1                 1
1                 2
2                 1
1                 2
2                 1
3                 1

Issue Table

ItemCode         issQty
--------         -------
1                 1
1                 2
2                 1
4                 1

I want to generate a report/view like ->

ItemCode          ItemDescr      GRN Qty      Issue Qty  
--------          ---------      -------      ---------         
1                  Test1          5              3                 
2                  Test2          2               1                 
3                  Test3          1               0
4                  Test4          0               1
5                  Test5          0               0

For this I have used the following sql code:

select a.ItemCode, a.ItemDescr, isnull(sum(b.grnQty),0) as 'GRN Qty', isnull(sum(c.issQty),0) as 'Issue Qty' from
ItemMaster a
left join GRN b
on a.ItemCode=b.ItemCode
left join Issue c
on a.ItemCode=c.ItemCode
group by a.ItemCode, a.ItemDescr

But the report generated is

ItemCode          ItemDescr      GRN Qty      Issue Qty  
--------          ---------      -------      ---------         
1                  Test1          10              9                 
2                  Test2          2               2                 
3                  Test3          1               0
4                  Test4          0               1
5                  Test5          0               0

What is wrong with my code???

It may be just a coincident that GRN Qty 10=5 X ( no of rows in Issue table for Item code 1) and same for other values like Issue qty 9 =3 X ( no of rows in GRN table for Item code 2)

Kindly help.

Answer 1

No concidence here, what you describe is exactly your problem. Please look at the result of the simplified query:

select a.ItemCode, a.ItemDescr, b.grnQty as 'GRN Qty', c.issQty as 'Issue Qty' 
from ItemMaster a
left join GRN b on a.ItemCode=b.ItemCode
left join Issue c on a.ItemCode=c.ItemCode

This table will look like (just the lines for ItemCode=1):

ItemCode          ItemDescr      GRN Qty      Issue Qty  
--------          ---------      -------      ---------         
1                  Test1         1            1                 
1                  Test1         2            1                 
1                  Test1         2            1                 
1                  Test1         1            2                 
1                  Test1         2            2                 
1                  Test1         2            2                

Now add your group by + summing end you end up where you are.

So you cannot do the sums directly over a select with both joins, because you end up with all permutations of the joins.

So subqueries can be of help here:

select 
    a.ItemCode, 
    a.ItemDescr, 
    (select sum(b.grnQty) from GRN b where a.ItemCode=b.ItemCode) as 'GRN Qty', 
    (select sum(c.issQty) from Issue c where a.ItemCode=c.ItemCode) as 'Issue Qty' 
from ItemMaster a