question

By referencing `X.TRANS` in the where clause, you have forced your `OUTER JOIN` to behave like an `INNER JOIN` If `T.Date_Out IS NOT NULL` (a column associated with transaction activity) then you must have a Location with transaction activity. The same is true of the other transaction activity column in the `WHERE` clause: `T.Reversed` If you truly want all locations whether they have transaction activity or not, you cannot look for non-null transaction activity columns in the where clause [edit] To perform an `OUTER JOIN` on table T, you cannot refer to table T in the `WHERE` clause. Not even once. If you ever refer to table T in the `WHERE` clause, you will lose rows from table L. It looks like you're looking for counts based on date ranges and byte flags in table `T`. Instead of filtering these in the `WHERE` clause, you can filter them in the `ON` clause. This will allow your query to return all the rows from L so you get all locations, and it will filter your counts from table T appropriately. Try this for your `FROM` and `WHERE.` I've moved all of the `T.` references into the `ON` clause: FROM X.LOCATIONS AS L LEFT OUTER JOIN X.TRANS AS T ON L.LocationID = T.LocationID AND (T.Date_Out IS NOT NULL) AND (T.Reversed = 'N') AND (T.Date_Out BETWEEN @BeginDate AND DATEADD(dd,1,@EndDate)) WHERE (L.LocType = 'Process' AND L.Enabled='Y') [/edit]

I can't post comments yet so I've had to post a new answer. Your revised query in the comment to KenJ still references T in the WHERE clause. An alternative is to use a subquery to get the rows you need from trans and then outer join that to Locations. Example SELECT L.LocationID, L.Location,L.Code,Count( t.sn) FROM LOCATIONS L LEFT JOIN (SELECT sn,LocationID FROM TRANS WHERE Reversed='N' AND Date_Out Between @BeginDate AND DateAdd(dd,1,@EndDate)) T ON L.LocationID=T.LocationID WHERE L.LocType='Process' AND L.Enabled='Y' GROUP BY L.LocationID,L.Location,L.Code ORDER BY L.LocationID

You could also just do a left join and instead of counting run a SUM() with case statement: DECLARE @Locations AS TABLE (LocationId int PRIMARY KEY CLUSTERED , Location varchar(100), Code int, LocType varchar(20), [Enabled] char(1)) DECLARE @Trans AS TABLE (sn int, LocationId int, Date_Out datetime, Reversed char(1), PRIMARY KEY CLUSTERED (locationid,sn)) ; INSERT INTO @Locations (LocationId,Location,Code,LocType,[Enabled]) SELECT 1,'Location 1',1,'Process','Y' UNION ALL SELECT 2,'Location 2',1,'Process','N' UNION ALL SELECT 3,'Location 3',1,'Not Process','Y' UNION ALL SELECT 4,'Location 4',1,'Not Process','N' ; INSERT INTO @Trans (sn, LocationId, Date_Out, Reversed) SELECT 1,1,'2011-01-01','N' UNION ALL SELECT 2,1,'2011-02-02','Y' UNION ALL SELECT 3,1,'2011-02-03','N' UNION ALL SELECT 4,3,'2011-02-01','N' UNION ALL SELECT 5,4,'2011-02-01','N' ; DECLARE @BeginDate datetime, @EndDate datetime SELECT @BeginDate = '2011-02-01', @EndDate = '2011-02-28' SELECT L.LocationID AS LocID, L.Location, L.Code AS LocCode, SUM(CASE WHEN (L.LocType = 'Process' AND L.Enabled = 'Y' AND (T.Date_Out IS NOT NULL) AND (T.Reversed = 'N') AND (T.Date_Out BETWEEN @BeginDate AND DATEADD(dd, 1, @EndDate))) THEN 1 ELSE 0 END) FROM @Locations AS L LEFT OUTER JOIN @Trans T ON L.LocationID = T.LocationID GROUP BY L.LocationID, L.Location, L.Code ORDER BY LocID As long as both tables are suitably indexed (clustered by LocationId would be good here) the optimiser will run through both tables (table/ clustered index scan) then join the tables and calculate the count. With a large dataset this method should be quicker.