Hi ALL,

I would like to LEFT JOIN two tables but the following two scripts one does and another one doesn’t. It looks first one is OK because I put the result to a temperate table and then I made the temperate table to join with another table without a where clause. The second script made the two tables directly left join with a where clause but it doesn’t doing LEFT JOIN. (The results only include rows where the joined fields from both tables are equal.)

Any help would be really appreciated.


*** First Script1 ***
CREATE TABLE #TblABC (DeptID smallint, NetEowInvRtlAmt money)

INSERT #TblABC
select DeptID, sum(NetEowInvRtlAmt)
from tblSLActuals
where (storeno = 990 or storeno = 995)
and FiscalWkEndDte >= Convert (datetime, '9/6/99&#39
and FiscalWkEndDte <= Convert (datetime, &#39;9/11/99&#39
group by DeptID

select vw.DeptID, NetEowInvRtlAmt
from vw_Department vw
LEFT JOIN #TblABC ON (#TblABC.DeptID = vw.DeptID)


***Second Script2 ***
select vw.DeptID, sum(NetEowInvRtlAmt)
from vw_Department vw
LEFT JOIN tblSLActuals ON (tblSLActuals.DeptID = vw.DeptID)
where (storeno = 990 or storeno = 995)
and FiscalWkEndDte >= Convert (datetime, &#39;9/6/99&#39
and FiscalWkEndDte <= Convert (datetime, &#39;9/11/99&#39
group by vw.DeptID



Stella Liu