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'
and FiscalWkEndDte <= Convert (datetime, '9/11/99'
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, '9/6/99'
and FiscalWkEndDte <= Convert (datetime, '9/11/99'
group by vw.DeptID
Stella Liu