sql - Why are redundant records being inserted with this TSQL Stored Procedure? -

when run portion of stored procedure in linqpad:

create table #temp1 (     memberno varchar(25),     memberitemcode varchar(25),     shortname varchar(25),     itemcode varchar(50),     wvitem varchar(25),     wvitemcode varchar(25),     wvdescription varchar(250),     week1usage varchar(25),     week1price varchar(25) )  insert #temp1 (memberno, memberitemcode, wvitemcode, wvdescription, week1usage, week1price)  select memberno, itemcode, wvitemcode, description, sum(qtyshipped), price invoicedetail  unit=@unit , invoicedate between @begin , @enddate group description, memberno, itemcode, wvitemcode, price  select * #temp1 order memberitemcode; 

...i hope for, namely 1 row of data each memberitemcode, qtyshipped summed in column.

however, when populate related table "week2" values, , combine them, this:

create procedure [dbo].[pricevariancetest]     @unit varchar(25),     @begdate datetime,     @enddate datetime    declare @week1end datetime = dateadd(day, 6, @begdate); declare @week2begin datetime = dateadd(day, 7, @begdate);  . . .  create table #temp2 (     memberno varchar(25),     week2usage varchar(25),     week2price varchar(25) )  -- populate week2 vals insert #temp2 (memberno, week2usage, week2price) select memberno, sum(qtyshipped), price invoicedetail  unit=@unit , invoicedate between @week2begin , @enddate group description, memberno, price;  -- have records week1 vals, , week2 vals; combine them third table create table #tempcombined (     memberno varchar(25),     memberitemcode varchar(25),     shortname varchar(25),     itemcode varchar(50),     wvitem varchar(25),     wvitemcode varchar(25),     wvdescription varchar(250),     week1usage varchar(25),     week2usage varchar(25),     usagevariance varchar(25),     week1price varchar(25),     week2price varchar(25),     pricevariance varchar(25),     percentageofpricevariance varchar(25) )  insert #tempcombined (memberno, memberitemcode, wvdescription, week1usage, week1price, week2usage, week2price) select t1.memberno, t1.memberitemcode, t1.wvdescription, t1.week1usage, t1.week1price, t2.week2usage, t2.week2price #temp1 t1 join #temp2 t2 on t1.memberno = t2.memberno 

...the result set has gone far south can see kudzu growing on it: 40 milliion records each memberitemcode, instead of summation , grouping working should.

why case when i'm summing/grouping second temp table, too, , combining 2 tables third 1 , returning that?

update

if convert join left join, solves problem i'm bemoaning, week2 values (week2usage , week2price) go silent/dark.

update 2

i hoping justin cave's comment key issue, changed code this:

-- populate week2 vals insert #temp2 (memberno, itemcode, wvdescription, week2usage, week2price) select memberno, itemcode, description, sum(qtyshipped), price invoicedetail  unit=@unit , invoicedate between @week2begin , @enddate group memberno, itemcode, description, price;  -- have records week1 vals, , week2 vals; combine them third table create table #tempcombined (     memberno varchar(25),     memberitemcode varchar(25),     shortname varchar(25),     itemcode varchar(50),     wvitem varchar(25),     wvitemcode varchar(25),     wvdescription varchar(250),     week1usage varchar(25),     week2usage varchar(25),     usagevariance varchar(25),     week1price varchar(25),     week2price varchar(25),     pricevariance varchar(25),     percentageofpricevariance varchar(25) )  insert #tempcombined (memberno, memberitemcode, wvdescription, week1usage, week1price, week2usage, week2price) select t1.memberno, t1.memberitemcode, t1.wvdescription, t1.week1usage, t1.week1price, t2.week2usage, t2.week2price #temp1 t1 left join #temp2 t2 on t1.itemcode = t2.itemcode , t1.memberno = t2.memberno , t1.wvdescription = t2.wvdescription 

...but makes no difference - "week2" vals still null.

update 3

by changing join type full outer join, hundreds of records week 1 vals , no week 2 vals, , hundreds of records week 2 vals , no week 1 vals, none have values both weeks.

the count of #tempcombined count of #temp1 , #temp2 combined (iow, piled together, instead of merged).

update 4

i won't able test until monday, i'm thinking maybe work:

insert temp1 (memberitemcode, week1usage, week1price) select memberitemcode, sum(qtyshipped), price invoicedetail  unit=@unit , invoicedate between @begdate , @week1end group memberitemcode, price  insert temp2 (memberitemcode, week2usage, week2price) select memberitemcode, sum(qtyshipped), price invoicedetail  unit=@unit , invoicedate between @week2begin , @enddate group memberitemcode, price  insert temp3 (memberitemcode, week1usage, week1price, week2usage, week2price) select t1.memberitemcode, t1.week1usage, t1.week1price, t2.week2usage, t2.week2price temp1 t1 left join temp2 t2 on t1.memberitemcode = t2.memberitemcode