SQL left outer join's unexpected output

Question

I have these tables, rolls and rollsout. I would like to perform a left outer join .

rolls

          |type|height|weight|Rate|
          -------------------------
          |RP  |2ft   | 200  | 100|
          |RP  |2ft   | 200  | 100|
          |RP  |2ft   | 200  | 100|
          |LD  |2ft   | 100  | 130|

rollsout

          |type|height|weight|Rate|
          -------------------------
          |RP  |2ft   | 200  | 100|
          |RP  |2ft   | 200  | 100|

Expected output after SUMing, JOINing and GROUPings ==>

          |type|height|SUM(rolls.weight)|SUM(rollsout.weight)|
          ----------------------------------------------------
          |RP  |2ft   | 600             | 400                |
          |LD  |2ft   | 100             | NILL               |

---

My code:

 SELECT rolls.hight,rolls.type,SUM(rolls.weight),SUM(rollsout.weight)
 FROM rolls 
 LEFT OUTER JOIN rollsout 
 ON rolls.hight = rollsout.hight AND rolls.type= rollsout.type 
 GROUP BY rolls.hight,rolls.type

But the O/P for the above code is

          |type|height|SUM(rolls.weight)|SUM(rollsout.weight)|
          ----------------------------------------------------
          |RP  |2ft   | 1200            | 1200               |
          |LD  |2ft   | 100             | NILL               |

I don't know where I am going wrong — can you explain?

Answer 1

You are not doing anything wrong. That's the behaviour of JOINs

It is number of rows on the left X number of rows on the right and in your case, it 3 x 2 = 6. And 6 x 200 = 1200

try this

Select rolls.height,rolls.type, SUM(rolls.weight) as W, rollsout.Ww
 FROM rolls 
 LEFT JOIN
       (Select height,type, SUM(weight) as Ww 
         From rollsout GROUP BY height, type
       ) as rollsout
ON rolls.height = rollsout.height AND 
rolls.type= rollsout.type 
GROUP BY rolls.height,rolls.type

SQLFiddle

I know this won't work in SQL Server, but it worked for MySQL