question

mzp avatar image
mzp asked

OUTER JOIN Not returning all rows

Would like consistent set of locations regardless of transaction activity. Query returns only rows with transaction activity. Please help me understand why outer join not working. thank you. -- query did NOT return all Locations SELECT L.LocationID AS LocID, L.Location, L.Code AS LocCode, count( t.sn) [Total Out] FROM X.LOCATIONS AS L LEFT OUTER JOIN X.TRANS AS T ON L.LocationID = T.LocationID WHERE (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)) GROUP BY L.LocationID, L.Location, L.Code ORDER BY LocID
outer-join
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
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]
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you KenJ. I tried same query removing date_out null criteria, results same: What I am trying to return is a consistent location list, with or without transaction activity. The report data is typically exported to Excel and missing location transaction causes users to complain they can't "cut and paste" results!! I tried union locations to trans but get duplicate null rows for non-transacted locations. I'm new to SQLServer. Thanks. SELECT L.LocationID AS LocID, L.Location, L.Code AS LocCode , count( t.sn) [Total Out] FROM SWIP.LOCATIONS AS L LEFT JOIN SWIP.TRANS AS T ON L.LocationID = T.LocationID WHERE (L.LocType = 'Process' AND L.Enabled='Y') AND (T.Reversed = 'N') AND (T.Date_Out BETWEEN @BeginDate AND DATEADD(dd,1,@EndDate)) GROUP BY L.LocationID, L.Location, L.Code ORDER BY LocID
0 Likes 0 ·
pipthegeek avatar image
pipthegeek answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@pipthegeek - Have a +1 to help you on your way to be able to comment.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.